Получить элемент с самой новой датой
Я пытаюсь получить элемент с самой новой датой из различных массивов JSON. Но я не могу заставить его работать. Он возвращает все элементы из массивов. Я пробовал несколько вещей с MAX, TOP(1), ORDER BY, но безуспешно. Может кто-нибудь указать мне в правильном направлении.
Вот что возвращает запрос
И это то, что мне бы хотелось — элемент, где «gyldigTil» — самая новая дата.
Это запрос, который я использую
DECLARE @json NVARCHAR(MAX) =
N'{
"_index": "cvr-v-20220630",
"_type": "_doc",
"_id": "4006567262",
"_score": 1.0,
"_source": {
"Vrvirksomhed": {
"virksomhedsform": [
{
"virksomhedsformkode": 80,
"langBeskrivelse": "Anpartsselskab",
"kortBeskrivelse": "APS",
"sidstOpdateret": "2020-02-26T12:02:30+01:00",
"ansvarligDataleverandoer": "E&S",
"periode": {
"gyldigFra": "2016-06-10",
"gyldigTil": "2020-01-22"
}
},
{
"virksomhedsformkode": 10,
"langBeskrivelse": "Enkeltmandsvirksomhed",
"kortBeskrivelse": "ENK",
"sidstOpdateret": "2020-03-11T12:08:14+01:00",
"ansvarligDataleverandoer": "T&S",
"periode": {
"gyldigFra": "2020-01-23",
"gyldigTil": "2022-12-31"
}
}
],
"virksomhedsstatus": [
{
"sidstOpdateret": "2018-10-12T23:46:13+02:00",
"status": "NORMAL",
"periode": {
"gyldigFra": "2016-06-10",
"gyldigTil": "2018-10-08"
}
},
{
"sidstOpdateret": "2020-02-26T12:02:30+01:00",
"status": "UNDER KONKURS",
"periode": {
"gyldigFra": "2018-10-09",
"gyldigTil": "2020-01-21"
}
},
{
"sidstOpdateret": "2020-02-26T12:02:30+01:00",
"status": "OPLØST EFTER KONKURS",
"periode": {
"gyldigFra": "2020-01-22",
"gyldigTil": "2020-01-22"
}
}
],
"cvrNummer": 37803472,
"virksomhedMetadata": {
"nyesteHovedbranche": {
"sidstOpdateret": "2020-02-26T12:02:30+01:00",
"branchetekst": "Vejgodstransport",
"branchekode": "494100",
"periode": {
"gyldigFra": "2016-06-10",
"gyldigTil": "2020-01-22"
}
}
}
}
}
}';
SELECT cvrNummer,
branchetekst,
branchekode,
[Status],
VF.kortBeskrivelse,
VF.gyldigTil AS VF_gyldigTil,
VS.gyldigTil AS VS_gyldigTil
FROM OPENJSON(@JSON)
WITH (
cvrNummer NVARCHAR(50) '$."_source"."Vrvirksomhed"."cvrNummer"',
branchetekst NVARCHAR(255) '$."_source"."Vrvirksomhed"."virksomhedMetadata"."nyesteHovedbranche"."branchetekst"',
branchekode NVARCHAR(255) '$."_source"."Vrvirksomhed"."virksomhedMetadata"."nyesteHovedbranche"."branchekode"',
virksomhedsform NVARCHAR(MAX) '$."_source"."Vrvirksomhed"."virksomhedsform"' AS JSON,
virksomhedsstatus NVARCHAR(MAX) '$."_source"."Vrvirksomhed"."virksomhedsstatus"' AS JSON
)
CROSS APPLY OPENJSON(virksomhedsform)
WITH (
kortBeskrivelse NVARCHAR(50) '$.kortBeskrivelse',
gyldigTil date '$.periode.gyldigTil'
) VF
CROSS APPLY OPENJSON(virksomhedsstatus)
WITH (
[Status] NVARCHAR(50) '$.status',
gyldigTil date '$.periode.gyldigTil'
) VS
Ответы 1
Один из подходов — обернуть запрос в CTE с помощью ROW_NUMBER() OVER(ORDER BY gyldigTil DESC) и отфильтровать результат с номером 1.
WITH cte_all AS (
SELECT cvrNummer,
branchetekst,
branchekode,
Status,
gyldigTil,
ROW_NUMBER() OVER(ORDER BY gyldigTil DESC) AS row_num
FROM OPENJSON(@JSON)
WITH (
cvrNummer NVARCHAR(50) '$."_source"."Vrvirksomhed"."cvrNummer"',
branchetekst NVARCHAR(255) '$."_source"."Vrvirksomhed"."virksomhedMetadata"."nyesteHovedbranche"."branchetekst"',
branchekode NVARCHAR(255) '$."_source"."Vrvirksomhed"."virksomhedMetadata"."nyesteHovedbranche"."branchekode"',
virksomhedsstatus NVARCHAR(MAX) '$."_source"."Vrvirksomhed"."virksomhedsstatus"' AS JSON
)
CROSS APPLY OPENJSON(virksomhedsstatus)
WITH (
[Status] NVARCHAR(50) '$.status',
gyldigTil date '$.periode.gyldigTil'
)
)
SELECT cvrNummer,
branchetekst,
branchekode,
Status,
gyldigTil
FROM cte_all
WHERE row_num = 1;
Обновлено:
Приведенный выше запрос обрабатывает вариант использования одного массива, возвращая одноэлементные атрибуты плюс атрибуты virksomhedsstatus из последней записи массива gyldigTil.
Аналогичный подход можно использовать и для дополнительных массивов. Однако, поскольку несколько предложений CROSS APPLY возвращают декартово произведение записей из обоих массивов (6 строк), а желаемая запись из каждого массива может находиться в разных строках, необходимо выбирать атрибуты из каждого массива независимо для одной строки из каждый массив и результат одной строки. Один из способов добиться этого — CTE для каждого массива, каждый из которых возвращает последнюю строку:
WITH cte_all AS (
SELECT Vrvirksomhed.cvrNummer,
Vrvirksomhed.branchetekst,
Vrvirksomhed.branchekode,
VS.Status,
VF.kortBeskrivelse,
VF.gyldigTil AS VF_gyldigTil,
VS.gyldigTil AS VS_gyldigTil,
ROW_NUMBER() OVER(ORDER BY VF.gyldigTil DESC) AS VF_row_num,
ROW_NUMBER() OVER(ORDER BY VS.gyldigTil DESC) AS VS_row_num
FROM OPENJSON(@JSON)
WITH (
cvrNummer NVARCHAR(50) '$."_source"."Vrvirksomhed"."cvrNummer"',
branchetekst NVARCHAR(255) '$."_source"."Vrvirksomhed"."virksomhedMetadata"."nyesteHovedbranche"."branchetekst"',
branchekode NVARCHAR(255) '$."_source"."Vrvirksomhed"."virksomhedMetadata"."nyesteHovedbranche"."branchekode"',
virksomhedsform NVARCHAR(MAX) '$."_source"."Vrvirksomhed"."virksomhedsform"' AS JSON,
virksomhedsstatus NVARCHAR(MAX) '$."_source"."Vrvirksomhed"."virksomhedsstatus"' AS JSON
) AS Vrvirksomhed
CROSS APPLY OPENJSON(virksomhedsform)
WITH (
kortBeskrivelse NVARCHAR(50) '$.kortBeskrivelse',
gyldigTil date '$.periode.gyldigTil'
) AS VF
CROSS APPLY OPENJSON(virksomhedsstatus)
WITH (
Status NVARCHAR(50) '$.status',
gyldigTil date '$.periode.gyldigTil'
) AS VS
)
,cte_VF AS (
SELECT cvrNummer,
branchetekst,
branchekode,
kortBeskrivelse,
VF_gyldigTil
FROM cte_all
WHERE VF_row_num = 1
)
,cte_VS AS (
SELECT VS_gyldigTil,
Status
FROM cte_all
WHERE VS_row_num = 1
)
SELECT cte_VF.cvrNummer,
cte_VF.branchetekst,
cte_VF.branchekode,
cte_VS.Status,
cte_VF.kortBeskrivelse,
cte_VF.VF_gyldigTil,
cte_VS.VS_gyldigTil
FROM cte_VS
CROSS JOIN cte_VF;
Имейте в виду, что результаты не являются детерминированными (произвольными), если существует несколько записей с одним и тем же последним значением gyldigTil.
Привет, Дэн. Да, это решит проблему. Однако я забыл упомянуть (извините), что строка JSON может содержать другие массивы, из которых мне также нужно получить самый новый элемент. И я не думаю, что ваше решение справится с этим. Поэтому я надеялся, что вместо этого его можно реализовать в разделе CROSS APPLY и, таким образом, я смогу сделать то же самое для других массивов в строке JSON. Надеюсь, что вышеизложенное имеет смысл.
@OJSlott, обновите свой вопрос, используя образец json с другими массивами. Возможно, дело просто в добавлении PARTITON BY к предложению OVER.
Я обновил описание, дампы экрана и код.
@OJSlott, вы сможете добавить еще один ROW_NUMBER() поверх gyldigTil из другого массива, а затем взять строку, где оба номера строк равны 1
@Dan, поскольку каждый элемент массива никогда не может иметь gyldigTil с одинаковыми датами, я добавил дополнительный ROW_NUMBER() поверх gyldigTil для другого массива, и это, похоже, решило мою проблему.
Пожалуйста, не используйте изображения для данных, используйте форматированный текст.