JSON Object Query SQL Server

I have a JSON string which is the following:

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

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! :)




  • 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

      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;