如何从JSON数组中获取gyldigTil最新日期的元素？

你的查询返回所有数组元素是因为直接对整个数组使用了CROSS APPLY，导致两个数组的所有元素进行笛卡尔积关联。要获取每个数组中gyldigTil最新的元素，需要先对每个数组单独过滤出最新条目，再关联到主数据。

修正思路 #

• 对virksomhedsform数组，通过TOP 1结合ORDER BY gyldigTil DESC筛选出日期最新的条目
• 对virksomhedsstatus数组执行同样的筛选操作
• 将这两个筛选后的结果与主数据关联，而非直接关联整个数组

修正后的SQL代码 #

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, 
       VS.[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
         )
    -- 筛选virksomhedsform中gyldigTil最新的条目
    CROSS APPLY (
        SELECT TOP 1 
            kortBeskrivelse, 
            gyldigTil
        FROM OPENJSON(virksomhedsform)
        WITH (
              kortBeskrivelse NVARCHAR(50) '$.kortBeskrivelse',
              gyldigTil date '$.periode.gyldigTil'
             )
        ORDER BY gyldigTil DESC
    ) VF 

    -- 筛选virksomhedsstatus中gyldigTil最新的条目
    CROSS APPLY (
        SELECT TOP 1 
            [Status], 
            gyldigTil
        FROM OPENJSON(virksomhedsstatus)
        WITH (
              [Status] NVARCHAR(50) '$.status',
              gyldigTil date '$.periode.gyldigTil'
             )
        ORDER BY gyldigTil DESC
    ) VS

补充说明 #

如果存在多个条目拥有相同的最新gyldigTil日期，且需要返回所有这些条目，可以改用ROW_NUMBER()函数进行分组排序：

-- 以virksomhedsform为例
CROSS APPLY (
    SELECT 
        kortBeskrivelse, 
        gyldigTil,
        ROW_NUMBER() OVER (ORDER BY gyldigTil DESC) AS rn
    FROM OPENJSON(virksomhedsform)
    WITH (
          kortBeskrivelse NVARCHAR(50) '$.kortBeskrivelse',
          gyldigTil date '$.periode.gyldigTil'
         )
) VF
WHERE VF.rn = 1

内容的提问来源于stack exchange，提问作者OJ Slott