Convert JSON value in single row in TSQL 2019

Question

I need help in transforming a string to json to put the values in separated lines. Using tsql (2019)

How do I have to transform the provided data (payload) into a json to create the expected result or is there another way to splitt the data?

Thank you in advanced.

AS for the main keys it works but I would like to have the interests in separated lines as well current result:

CustomerID	payload	payload_json	key	value
10001	{diet:[vegetarian],interest:[cooking,fitness,technology,interior,diy,talk,gaming,fashion]}	{"diet":["vegetarian"],"interest":["cooking","fitness","technology","interior","diy","talk","gaming","fashion"]}	diet	vegetarian
10001	{diet:[vegetarian],interest:[cooking,fitness,technology,interior,diy,talk,gaming,fashion]}	{"diet":["vegetarian"],"interest":["cooking","fitness","technology","interior","diy","talk","gaming","fashion"]}	interest	["cooking","fitness","technology","interior","diy","talk","gaming","fashion"]

Expected Result:

CustomerID	Key	Value
10001	diet	vegetarian
10001	interest	Cooking
10001	interest	fitness
10001	interest	technology
.
.
.

What's the relevance of `payload`if it's not valid JSON? `value` does not seem to be valid JSON? Which of these columns do you actually want to parse, please explain the logic? And why does row 2 only have three JSON values parsed out? — Charlieface, May 22 '23 at 12:02
the "simple" payload - field is what i got and payload_json is what I have created by using some replace-statements. The two lines is the result of the previous amendments I performed. And now I am stuck and would like to have the values of the second line in separated lines too. — Andreas Ketelhut, May 22 '23 at 12:27
So why have you stopped at only three rows for the second sample row? Shouldn't it be six? — Charlieface, May 22 '23 at 12:58

score 1 · Accepted Answer · answered May 22 '23 at 12:05

1

It seems you just want a dynamic OPENJSON call.

SELECT
  t.CustomerID,
  t.[key],
  j.value
FROM YourTable t
CROSS APPLY OPENJSON(t.payload_json, '$.' + t.[key]) j;

db<>fiddle

What the relevance of the payload and value columns are to the question I don't know, your logic is not clear.

answered May 22 '23 at 12:05

Charlieface

52,284
6
19
43

Payload is the source, Payload_json is my temporary result. and instead of multiple lines for the key "interest" I have all interests in one line. – Andreas Ketelhut May 22 '23 at 12:30

score 0 · Answer 2 · answered May 22 '23 at 12:12

You can do a little replace thing:

SELECT  replace(replace(replace(replace(replace(replace(replace(payload, '{', '{"'), ':', '":'), '[', '["'), '],', ']$'), ',', '","'), ']', '"]'), ']$', '],"')
FROM    (
    VALUES  (10001, N'{diet:[vegetarian],interest:[cooking,fitness,technology,interior,diy,talk,gaming,fashion]}')
) t (CustomerID,payload)

This converts the payload column into a json which you can use together with openjson.

The replace won't work if you have embedded "," etc, but for simple values it might be good enough. You oughtta get rid of this weird format and use json right away though

Convert JSON value in single row in TSQL 2019

2 Answers2