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

Convert JSON value in single row in TSQL 2019

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
.
.
.
Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search