Querying nested Json with SQL Server

Question

I have this content in a table in a column 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 querying nested Json, any tips?

Thanks!

SQL Server 2014 has no support for JSON. There are workarounds/hacks, but they're all painful. The best approach if you can't upgrade (which is recommended, since SQL Server 2014 has been out of support for 3 years now) is to leave the JSON parsing to the client side. — Jeroen Mostert
– Jeroen Mostert, Commented Sep 6, 2022 at 15:42

Meyssam Toluie · Accepted Answer · 2022-09-06 12:53:00Z

1

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;

answered Sep 6, 2022 at 12:53

Meyssam Toluie

1,0817 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Charlieface · Accepted Answer · 2022-09-06 15:36:57Z

1

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

edited Sep 6, 2022 at 15:36

answered Sep 6, 2022 at 13:15

Charlieface

78.5k8 gold badges35 silver badges77 bronze badges

4 Comments

ɐlǝx Over a year ago

I've got this: Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

Charlieface Over a year ago

Are you still running SQL Server 2014? Then this won't work, you should upgrade to a newer version

ɐlǝx Over a year ago

I can't upgrade :(

Charlieface Over a year ago

Only other option I can think is to write a SQLCLR function that uses Newtonsoft or similar to parse it. Or parse it client-side and send it as a Table Valued Parameter

Collectives™ on Stack Overflow

Querying nested Json with SQL Server

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related