Quering nested Json with SQL Server-CodePudding

I have this content in a table (column named JsonResponse)

{
  "results": {
    "meta": {
      "number_of_node": 2
    },
    "data": {
      "Node": [
        {
          "id": "44511",
          "subject": 31366
        },
        {
          "id": "72176",
          "subject": 36508
        }
      ],
    }
  }
}

I'm trying to extract ALL the "subject"; here my query:

SELECT 
  JSON_VALUE(JSonResponse, '$.results.data.Node.subject') AS JsonResponse
  from Table

but the query result is always null. I didn't find documentation about quering nested Json, any tips? Thanks!

CodePudding user response：

Try this:

DECLARE @Table TABLE (
    JSonResponse NVARCHAR(MAX)
);
INSERT @Table ( JSonResponse )
VALUES (
'{
  "results": {
        "meta": {
          "number_of_node": 2
        },
        "data": {
          "Node": [
            {
              "id": "44511",
              "subject": 31366
            },
            {
              "id": "72176",
              "subject": 36508
            }
          ]
        }
  }
}'

    );

SELECT oj2.*
FROM @Table AS t
     CROSS APPLY
    OPENJSON(t.JSonResponse, '$.results')
        WITH ( meta NVARCHAR(MAX) AS JSON, data NVARCHAR(MAX) AS JSON ) AS oj
     CROSS APPLY
    OPENJSON(oj.data, '$.Node')
        WITH ( id INT, subject NVARCHAR(MAX)) AS oj2;

CodePudding user response：

You don't necessarily need nested OPENJSON calls, as you can jump straight to the right path

SELECT oj.*
FROM @Table AS t
CROSS APPLY OPENJSON(t.JSonResponse, '$.results.data.Node')
  WITH (
    id int,
    subject int
  ) AS oj;

db<>fiddle