JSON Object Query SQL Server

Question

I have a JSON string which is the following:

[
    {
        "id": 103001058774,
        "name": "status",
        "label": "Status",
        "description": "Ticket status",
        "choices": {
            "2": [
                "Open",
                "Open"
            ],
            "3": [
                "Pending",
                "Pending"
            ],
            "4": [
                "Resolved",
                "Resolved"
            ],
            "5": [
                "Closed",
                "Closed"
            ],
            "6": [
                "Waiting on Customer",
                "Awaiting your Reply"
            ],
            "7": [
                "Waiting on Third Party",
                "Being Processed"
            ],
            "8": [
                "Assigned",
                "Assigned"
            ]
        }
    }
]

I am trying to put this into a SQL table looking like the following from the CHOICES leg of the JSON:

id	agent_label	customer_label
2	Open	Open
3	Pending	Pending
4	Resolved	Resolved
5	Closed	Closed
6	Waiting on Customer	Awaiting your Reply
7	Waiting on Third Party	Being Processed
8	Assigned	Assigned

I already have this as a Query and I am on the right lines... But I don't know how to strip out the ID numbers!:

DECLARE @jsonStatusesData NVARCHAR (MAX) = *'My JSON String'*

SELECT id = JSON_QUERY(j.value, $.choices')
FROM OPENJSON(@jsonStatusesData) AS j

I do have a few more lines in there I need to add in, but ultimately the table above is what I want to do. Of course, that SQL query is just returning the whole Choices branch and doesn't help me at all.

Any help would be great! :)

Thanks,

Ash

Hope this takes you further along: ```declare @choices varchar(max) SELECT @choices = JSON_QUERY(j.value, '$.choices') FROM OPENJSON(@jsonStatusesData) AS j SELECT a.[key], a.[value] FROM OPENJSON(@choices) as a``` — sudheeshix, Jun 27 '23 at 17:16

score 2 · Accepted Answer · answered Jun 27 '23 at 16:24

You need a second level of OPENJSON to break out the choices array. And you need to read that array using the AS JSON syntax

SELECT
  j1.id,
  j2.[key],
  agent_label = JSON_VALUE(j2.value, '$[0]'),
  customer_label = JSON_VALUE(j2.value, '$[1]')
FROM OPENJSON(@jsonStatusesData)
  WITH (
    id bigint,
    choices nvarchar(max) AS JSON
  ) j1
CROSS APPLY OPENJSON(j1.choices) j2;

db<>fiddle

GMB · Answer 2 · 2023-06-27T16:27:33.010

1

This does the job for your sample data:

select [key] as id, 
    json_value(value, '$[0]') as agent_label, 
    json_value(value, '$[1]') as customer_label
from openjson(@jsonStatusesData, '$[0].choices') AS j

Assumptions:

your json string is an array, with a single object at the top level (in other words, there is only one array of choices to unnest)
in the choices array, each object is an array of two scalar values that you want to put side by side

id	agent_label	customer_label
2	Open	Open
3	Pending	Pending
4	Resolved	Resolved
5	Closed	Closed
6	Waiting on Customer	Awaiting your Reply
7	Waiting on Third Party	Being Processed
8	Assigned	Assigned

fiddle

edited Jun 27 '23 at 16:27

answered Jun 27 '23 at 16:24

GMB

216,147
25
84
135

`openjson(@jsonStatusesData, '$[0].choices')` seems simpler. Note that this doesn't work if the outer array has multiple values. – Charlieface Jun 27 '23 at 16:25
@Charlieface: yes, thanks for the shorcut in `openjson`. And yes, this supposes only one object at the top level. – GMB Jun 27 '23 at 16:29

JSON Object Query SQL Server

2 Answers2