英文:
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.
使用tsql（2019）
如何将提供的数据（payload）转换为JSON以创建预期的结果，或者是否有其他方法可以拆分数据？
谢谢您的提前帮助。
关于主键，它可以工作，但我也希望将兴趣分开显示。
当前结果：
| CustomerID | Key | Value |
|---|---|---|
| 10001 | diet | vegetarian |
| 10001 | interest | cooking |
| 10001 | interest | fitness |
| 10001 | interest | technology |
预期结果：
| CustomerID | Key | Value |
|---|---|---|
| 10001 | diet | vegetarian |
| 10001 | interest | cooking |
| 10001 | interest | fitness |
| 10001 | interest | technology |
| . | ||
| . | ||
| . |
英文:
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 |
| . | ||
| . | ||
| . |
答案1
得分: 1
看起来你只想要一个动态的 OPENJSON 调用。
选择
t.CustomerID,
t.[key],
j.value
从你的表t
交叉应用OPENJSON(t.payload_json, '$.' + t.[key]) j;
“payload” 和 “value” 列与问题的相关性我不清楚，你的逻辑不清晰。
英文:
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;
What the relevance of the payload and value columns are to the question I don't know, your logic is not clear.
答案2
得分: 0
你可以进行一些替换操作：
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)
这将将payload列转换为JSON，您可以与openjson一起使用。
如果您有嵌入的“,”等字符，替换将无效，但对于简单的值，它可能足够好。不过，最好摆脱这种奇怪的格式，直接使用JSON。
英文:
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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论