Read JSON column from SQL Table and concat JSON array into string [duplicate]

Question

I'm trying to read data from a table who has multiple columns and one of them is JSON data. Inside this JSON there is an array of JSON's that I want to get one specific value.

The JSON looks like this:

{
    "FirstName": "Carlos",
    "LastName": "Perez",
    "PhoneNumbers": [{
        "PhoneNumber": "123456789"
    },
    {
        "PhoneNumber": "987654321"
    }]
}

What I want is to read this JSON column so it gives me only the phones numbers.

I want to get a result like this:

Id	Column 1	Column 2	JSON Column
1	Value 1	Value 2	123456789, 987654321

This looks like a simple query to get the data from the JSOn, using OPENJSON, and then string aggregation. There's plenty of examples.om how to do both of these things, what about the wealth of information out there didn't you understand? What about your attempts didn't work? What were your attempts? — Thom A
– Thom A ♦, Commented Dec 27, 2023 at 17:39

user3402754 · Accepted Answer · 2023-12-27 20:18:17Z

-1

Your question can be improved by including a snippet of what you've done/tried and perhaps the challenge with that you can get tailored help with context, in any case, here's a SQL query to achieve your desired result.

SELECT
    Id,
    Column1,
    Column2,
    JSON_QUERY(YourJSONColumn, '$.PhoneNumbers[*].PhoneNumber') AS PhoneNumbers,
    STUFF((
        SELECT ', ' + PhoneNumber
        FROM OPENJSON(YourJSONColumn, '$.PhoneNumbers')
        WITH (PhoneNumber NVARCHAR(255) '$.PhoneNumber')
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS ConcatenatedPhoneNumbers
FROM YourTable;

Remember to replace YourTable with the actual name of your table and YourJSONColumn with the name of your JSON column

Edit: Optimising the query

SELECT
    Id,
    Column1,
    Column2,
    JSON_QUERY(YourJSONColumn, '$.PhoneNumbers') AS PhoneNumbers,
    STRING_AGG(JSON_VALUE(phoneNumber.value, '$.PhoneNumber'), ', ') AS ConcatenatedPhoneNumbers
FROM YourTable
CROSS APPLY OPENJSON(YourJSONColumn, '$.PhoneNumbers') 
    WITH (PhoneNumber NVARCHAR(255) '$.PhoneNumber') AS phoneNumber
GROUP BY Id, Column1, Column2, YourJSONColumn;

edited Dec 27, 2023 at 20:18

answered Dec 27, 2023 at 17:51

user3402754

3,1052 gold badges21 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Thom A Over a year ago

Why use the old FOR XML PATH method here?

Martin Smith Over a year ago

I don't see any sign that JSON_QUERY supports *

Bruno Gonzalez Torres Over a year ago

I had to make some adjustments, but it helped a lot! Thanks for your answer!

user3402754 Over a year ago

@MartinSmith @ThomA using the FOR XML PATH method to concatenate phone numbers from a JSON column and then applying JSON_QUERY to extract an array of phone numbers. The STUFF function is used to remove the leading comma from the concatenated phone numbers. It's important to note that there was no context or way to measure the challenge faced and so it was a long wild shot

Collectives™ on Stack Overflow

Read JSON column from SQL Table and concat JSON array into string [duplicate]

1 Answer 1

4 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Linked

Related