Extract Data from multiple objects in JSON variable in SQL

user_9668151 0
Viewed 581

New to JSON and I am trying to pull information from multiple JSON objects into one table in SQL, below is a small snippet of me declaring the variable:

DECLARE @json NVARCHAR(MAX);
SET @json = N'{
    "result": {
        "6828": {
            "management_account_id": "6828",
            "management_account_label": "EXC001-00-GP Excellerate Facilities",
            "management_account_code": "EXC001-00-GP"
        },
        "12183": {
            "management_account_id": "12183",
            "management_account_label": "ENF001-04-GP The Zone ",
            "management_account_code": "ENF001-04-GP"

I can get the information I need from one object, example below 6828:

SELECT *
from OPENJSON(@json, '$.result')
with(
    id nvarchar(50) '$."6828".management_account_id',
    lbl nvarchar(50) '$."6828".management_account_label'
);

How would I go about adding 12183 or any other objects I might have to appear in the same column since the fields are the same.

sql-server sql json
1 Answers

You need a combination of:

  1. OPENJSON() with default schema. The result is a table with columns key, value and type. The key column contains the key of each nested JSON object, the value column contains the value of each nested JSON object.
  2. OPENJSON() with explicit schema (the WITH clause) and an additional APPLY operator to parse the nested JSON objects from the first OPEBNJSON() call using the defined output schema:
SELECT j2.*
FROM OPENJSON(@json, '$.result') j1
OUTER APPLY OPENJSON(j1.[value]) WITH (
    id nvarchar(50) '$.management_account_id',
    lbl nvarchar(50) '$.management_account_label'
) j2

Result:

id    lbl
-----------------------------------------
6828  EXC001-00-GP Excellerate Facilities
12183 ENF001-04-GP The Zone
user_65780800
Related