How to parse JSON array to SQL Table

Question

I'm stuck with some problem. I have no idea how to read all nodes in JSON objects list.

I have 3 items (objects) with headers [item1, item2, item3].

I need to get name of the item and all inner fields, like this: table screenshot

Here is source JSON data:

{
  "item1": {
    "title": "2",
    "value": null,
    "visible": true,
    "name": "item1",
    "enabled": true,
    "readonly": false,
    "id": "f1f46ce6-9d0b-4eaf-88b7-d35b23a4d2e4"
  },
  "item2": {
    "title": null,
    "value": null
    "visible": true,
    "name": "item2",
    "enabled": true,
    "readonly": false,
    "id": "da2b8a02-cfbd-4de8-8a33-74e2a484475a"
  },
  "item3": {
    "title": "",
    "value": null,
    "visible": true,
    "name": "item3",
    "enabled": true,
    "readonly": false,
    "id": "57ee45d6-41d7-45c2-b022-13220e31d2d2"
  }
}

I found approach how to do it with OPENJSON() or JSON_VALUE() functions, but I still can't iterate over all items..

  SELECT * FROM OPENJSON (@json, '$.item1')
    WITH (
        ItemName VARCHAR(100) '$',
        Title VARCHAR(100) '$.name',
        Value VARCHAR(100) '$.value',
        Visible VARCHAR(100) '$.visible',
        Name VARCHAR(100) '$.name',
        Enabled VARCHAR(100) '$.enabled',
        ReadOnly VARCHAR(100) '$.readonly',
        Id VARCHAR(500) '$.id'
    )

That is not an object list. It's an object of objects. Use the proper json syntax and this would be trivial. — Joe Phillips
– Joe Phillips, Commented May 30, 2018 at 14:40

Umair · Accepted Answer · 2018-05-30 13:59:06Z

2

Something like this?

SELECT [Key].[key] AS [ItemName], [Value].*
FROM OPENJSON (@json, '$') AS [Key]
CROSS APPLY OPENJSON([Key].value)
    WITH (
        Title VARCHAR(100) '$.title',
        Value VARCHAR(100) '$.value',
        Visible VARCHAR(100) '$.visible',
        Name VARCHAR(100) '$.name',
        Enabled VARCHAR(100) '$.enabled',
        ReadOnly VARCHAR(100) '$.readonly',
        Id VARCHAR(500) '$.id'
    ) AS [Value]

The "trick" is the cross apply on the sub-objects json graph.

answered May 30, 2018 at 13:59

Umair

3,2931 gold badge33 silver badges53 bronze badges

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

Collectives™ on Stack Overflow

How to parse JSON array to SQL Table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related