SQL将Json数组解析为行

正如我在评论中提到的,我会转而使用 WITH 子句和定义列及其数据类型。然后,您还可以使用以下命令将值分成两行。注:额外费用 OPENJSON 最后,哪一个治疗 custinfo 就像阵列一样;返回2行(数组中的每个值对应1行):

DECLARE @json NVARCHAR(MAX) ='{
  "customer": [
    {
      "id": "123",
      "history": [
        {
          "id": "a123",
          "dates": [
            {
              "date": "2022-03-19",
              "details": {
                "custinfo": [
                  "male",
                  "married"
                ],                
                "age": 40            
                }}]}]}]}';
                                             
SELECT c.id AS CustId,
       h.id AS CustId_Hist,
       d.date AS date,
       d.age AS age,
       ci.[value] AS custinfo
FROM OPENJSON( @json,'$.customer')
     WITH (id int,
           history nvarchar(MAX) AS JSON) c
     CROSS APPLY OPENJSON (c.history)
                 WITH (id varchar(10),
                       dates nvarchar(MAX) AS JSON) h
     CROSS APPLY OPENJSON (h.dates) 
                 WITH(date date,
                      details nvarchar(MAX) AS JSON,
                      age int '$.details.age') d
     CROSS APPLY OPENJSON(d.details,'$.custinfo') ci;