对多个JSON对象中的Json对象进行SQL查询 [英] SQL Query on Json object inside multiple JSON objects

问题描述

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

I have the below Json string. I need to write a query to get the TP records. Without providing the index value, we need get the result.

{
   "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值.

How do I query to get the TP values.

预期结果:

  TID    TName 
  11     TT1 
  12     TT2
  13     TT3
  14     TT4

推荐答案

您可以使用OPENJSON函数，其中包含WITH子句，并添加了与CROSS APPLY子句一样多的子句:

You can use OPENJSON function containing WITH Clause added as many CROSS APPLY Clause as upto see all sub-arrays :

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

演示

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

Update : If the first array S is fixed to contain only one item, we can reduce one-step as

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  

演示

这篇关于对多个JSON对象中的Json对象进行SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！