Question 110226 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

How can I extract GUID's from Dictionary stored in JSON field in SQL?

I have the following table:

Test_ID Test_JSON
some uniqueidentifer see below
some uniqueidentifier see below

The Test_JSON structure is as follows:

{
    "LaneIds": {
        "1": "a5a8ff96-0291-4ef9-890a-57c1874398bc",
        "2": "7a698a8b-934b-4a57-99c7-29a200d87877",
        "3": "aa30906d-e859-40a8-aedd-ae8ae10d0ae1",
        "4": "ccb05b71-a65a-4db7-b7c9-b4b7251150c7",
        "5": "c10593a9-06c3-49e4-bf4e-9d1c5ece98d4",
        "6": "6eeb9c85-f3ac-44cb-be74-f2d7554ea8d3",
        "7": "1226abb3-58c3-4b1e-844f-f2643d874490",
        "8": "7256ce43-014a-4dd6-a072-f8de2c373688"
    },
    "SomeOtherID": "b9d75ef9-b68d-46ef-9d34-3f5e99f448e2",
    "SomeOtherProperty": 12345
}

I need to get all lane IDs in a comma separated list for all Test_IDs. So my final output needs to be like this:

Test_ID Column B
some uniqueidentifier "a5a8ff96-0291-4ef9-890a-57c1874398bc, 7a698a8b-934b-4a57-99c7-29a200d87877, aa30906d-e859-40a8-aedd-ae8ae10d0ae1, etc."
next test_id lane_ids

What is the best way to do this using SQL/T-SQL?

I am able to get the LaneIds using JSON_QUERY() but I specifically need to drop the ints in the key field and only return the GUIDs in a comma separated list.

Tags:

2

Answers


  1. 0

    Since you mentioned you’re already using JSON_QUERY(), I’m guessing the problem is with the definition of your JSON path. Have you tried the following:
    JSON_QUERY(Test_JSON, '$.LaneIds[*]')? This would query the column you showed as JSON, look for the key LaneIds and get all values from within that key.

    Login or Signup to reply.
  2. 0

    This should get your values in all their denormalized goodness:

    declare @json nvarchar(max) = N'
{
    "LaneIds": {
        "1": "a5a8ff96-0291-4ef9-890a-57c1874398bc",
        "2": "7a698a8b-934b-4a57-99c7-29a200d87877",
        "3": "aa30906d-e859-40a8-aedd-ae8ae10d0ae1",
        "4": "ccb05b71-a65a-4db7-b7c9-b4b7251150c7",
        "5": "c10593a9-06c3-49e4-bf4e-9d1c5ece98d4",
        "6": "6eeb9c85-f3ac-44cb-be74-f2d7554ea8d3",
        "7": "1226abb3-58c3-4b1e-844f-f2643d874490",
        "8": "7256ce43-014a-4dd6-a072-f8de2c373688"
    },
    "SomeOtherID": "b9d75ef9-b68d-46ef-9d34-3f5e99f448e2",
    "SomeOtherProperty": 12345
}'

select string_agg(keyData, ',')  WITHIN GROUP ( ORDER BY keyData) AS IDs
  ,     string_agg(x.value, ',')  WITHIN GROUP ( ORDER BY keyData) AS guids
from openjson(@json, '$.LaneIds') x
cross apply (
    select cast(x.[key] AS INT) AS keyData
  ) kd
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search