Transform properties from JSON array into comma-separated string

Question

I have a string column in an SQL-Server table on Azure which contains the following data:

 {
        "status": "success",
        "data": [
            {
                "name": "Jane",
                "type": 0
            },
            {
                "name": "John",
                "type": 0
            }
        ]
    }

How can it be transformed into a comma-separated string containing "Jane, John"?

Is "data" always going to be the same size? For example will it always be two items or do you need it to be dynamic? — d0little
– d0little, Commented May 20, 2021 at 15:05

d0little · Accepted Answer · 2021-05-20 16:40:15Z

1

Here how to achieve this via a snippet of some of my older code, you should be able to loop through the table and do this for each row. There may be a quicker way of doing it but this will work.

DECLARE @JSON NVARCHAR(200) = '{"status": "success", "data": [{"name": "Jane", "type": 0},{"name": "John", "type": 0}]}',
    @result nvarchar(max) = ''


SELECT @result = @result + [value] + N', '
FROM (
    SELECT DISTINCT data.value
    FROM OPENJSON(@JSON, '$.data') as jsondata
    CROSS APPLY OPENJSON(jsondata.value) as data
    WHERE data.[key] = 'name') a

select @result = substring(@result, 1, (LEN(@result)-1))

select @result

answered May 20, 2021 at 16:40

d0little

4764 silver badges8 bronze badges

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

Collectives™ on Stack Overflow

Transform properties from JSON array into comma-separated string

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related