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

Answer 1

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