对多个JSON对象中的Json对象进行SQL查询

一尘不染

对多个JSON对象中的Json对象进行SQL查询

sql

我有下面的Json字符串。我需要编写查询以获取TP记录。在不提供索引值的情况下，我们需要获取结果。

{
   "S": [
    {
      "Name": "Project1",
      "SP": [
       {
          "ID": 1,
          "Name": "Test1",
          "TP": [
            {
              "TID": 11,
              "TName": "TT1",
            },
            {
              "TID": 12,
              "TName": "TT2",
            },
         ]
      },
      {
        "ID": 2,
        "Name": "Test2",
        "TP": [
         {
          "TID": 13,
          "TName": "TT3",
         },
         {
          "TID": 14,
          "TName": "TT4",
         },
       ]
    },
  ]}]}

如何查询以获取TP值。

预期结果：

  TID    TName 
  11     TT1 
  12     TT2
  13     TT3
  14     TT4

阅读 150

2021-05-16

共1个答案

一尘不染

您可以使用OPENJSON包含WITH添加了CROSS APPLY子句的子句的函数，最多可以看到所有子数组：

SELECT S3.TID, S3.TName
  FROM tab
 CROSS APPLY OPENJSON(JsonData)
             WITH ( S  nvarchar(max) '$.S' AS JSON) AS S0
 CROSS APPLY OPENJSON (S0.S) 
             WITH (
                    SP nvarchar(max) '$.SP' AS JSON ) S1
 CROSS APPLY OPENJSON (S1.SP) 
             WITH (
                    TP nvarchar(max) '$.TP' AS JSON ) S2
 CROSS APPLY OPENJSON (S2.TP) 
             WITH (
                    TID   nvarchar(500) '$.TID', 
                    TName nvarchar(500) '$.TName' ) S3

[Demo](https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=50a3068d9329a458b85f8459e252ccc4)

更新： 如果第一个数组S固定为仅包含一个项目，我们可以将一步减少为

SELECT S3.TID, S3.TName
  FROM tab
 CROSS APPLY OPENJSON(JsonData)
             WITH (
                    SP nvarchar(max) '$.S[0].SP' AS JSON ) S1
 CROSS APPLY OPENJSON (S1.SP) 
             WITH (
                    TP nvarchar(max) '$.TP' AS JSON ) S2
 CROSS APPLY OPENJSON (S2.TP) 
             WITH (
                    TID   nvarchar(500) '$.TID', 
                    TName nvarchar(500) '$.TName' ) S3

[Demo](https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=6cfa08d08003a7cad1594281a44eed96)

2021-05-16