Search code examples
jsonsql-servert-sqlsql-server-2019open-json

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
.
.
.
Solution

  • 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.