Retrieve values in a list in JSON using TSQL

Question

I have this JSON and I want to get all the values in keyPhrases to be displayed as a column in TSQL query. Do you know how it can be done?

Desired Output:

ID	keyPhrases
778124772	banker, good position, friends family

JSON:

{"documents": [{"id": "778124772", "keyPhrases": ["banker", "good position", "friends", "family"     ], "warnings": []}]}

Really appreciate your help.

What version of SQL Server? If you're not using 2016+ then i suggest not doing this in SQL Server, use a different language. if it is 2016+, what have you tried and why didn't it work, or what research havve you done and what didn't you understand about it? There's plenty of resources on how to use T-SQL's OPENJSON features. — Thom A
– Thom A ♦, Commented Dec 10, 2020 at 22:55

Emin Mesic · Accepted Answer · 2020-12-10 23:05:03Z

2

The OPENJSON can help you, so the potential solution can be:

DECLARE @json NVARCHAR(MAX) = '{"documents": [{"id": "778124772", "keyPhrases": ["banker", "good position", "friends", "family"     ], "warnings": []}]}';

SELECT 
        R.id as ID, 
        (SELECT STRING_AGG(value, ', ') FROM OPENJSON(R.kp)) as keyPhrases
FROM OPENJSON(@json, '$.documents') 
     WITH (id bigint, kp nvarchar(max) '$.keyPhrases' as JSON) R

Just to know, OPENJSON and STRING_AGG are features related to newer versions of SQL Server.

answered Dec 10, 2020 at 23:05

Emin Mesic

1,8112 gold badges10 silver badges19 bronze badges

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

Comments

Ilyes · Accepted Answer · 2020-12-10 23:29:49Z

0

You can use OPENJSON() and STRING_AGG() as the following:

DECLARE @JSON NVARCHAR(MAX) = N'{"documents": [{"id": "778124772", "keyPhrases": ["banker", "good position", "friends", "family"     ], "warnings": []}]}';

SELECT JSON_VALUE(T.Value, '$.id') AS ID,
       STRING_AGG(TT.Value, ',') AS keyPhrases
FROM OPENJSON(@JSON, '$.documents') AS T
CROSS APPLY OPENJSON(Value, '$.keyPhrases') AS TT
GROUP BY JSON_VALUE(T.Value, '$.id');

Here is a db<>fiddle

answered Dec 10, 2020 at 23:29

Ilyes

15k5 gold badges31 silver badges61 bronze badges

Collectives™ on Stack Overflow

Retrieve values in a list in JSON using TSQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related