Question 195301 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

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": [
                "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

Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search