MySQL JSON键值对与表

发布于 2025-01-28 08:43:13 字数 444 浏览 3 评论 0原文

对于第一阶段，我建议未嵌套JSON -PLAIAS对键值从PHP脚本填充的POST请求包装，并以单个JSON型参数传递到MySQL存储的过程 - 我需要获取表格表 - 两个列中的两个列 - 键和相应的值

set @j:='{"customer_name":"Ворпенсий", "customer_bd":"1952-10-07"}';
select *, json_unquote(json_extract(@j, concat('$.', `name`))) as `value`
from json_table(json_keys(@j), 
              "$[*]" columns(name varchar(100) path "$")) as jt;

代码作为工作期望但是如何将此两步解决方案加入1步？

原文

for first stage i suggest that json not nested - plain pairs key-value packed from post request by php script and passed as single json-type parameter to mysql stored procedure - i need get table where two columns - key and corresponding value

set @j:='{"customer_name":"Ворпенсий", "customer_bd":"1952-10-07"}';
select *, json_unquote(json_extract(@j, concat('$.', `name`))) as `value`
from json_table(json_keys(@j), 
              "$[*]" columns(name varchar(100) path "quot;)) as jt;

code work as expected but how can i join this 2-step solution to 1-step?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

鱼忆七猫命九 2025-02-04 08:43:13

您正在做的事情可能接近最佳。您也可以通过连接两个JSON_TABLES，一个键和另一个值来实现相同的结果。例如：

SELECT name, value
FROM JSON_TABLE(JSON_KEYS(@j),
    "$[*]" COLUMNS (
          rn FOR ORDINALITY,
          name VARCHAR(100) PATH '
输出（对于您的示例数据）：
name            value
customer_bd     1952-10-07
customer_name   Ворпенсий



          )
    ) n
JOIN JSON_TABLE(@j, 
    "$.*" COLUMNS (
          rn FOR ORDINALITY,
          value VARCHAR(100) PATH '
输出（对于您的示例数据）：



          )
    ) v ON n.rn = v.rn

输出（对于您的示例数据）：

What you are doing is probably close to optimal. You could also achieve the same result by joining two JSON_TABLEs, one of keys and the other of values. For example:

SELECT name, value
FROM JSON_TABLE(JSON_KEYS(@j),
    "$[*]" COLUMNS (
          rn FOR ORDINALITY,
          name VARCHAR(100) PATH '
Output (for your sample data):
name            value
customer_bd     1952-10-07
customer_name   Ворпенсий

Demo on dbfiddle

          )
    ) n
JOIN JSON_TABLE(@j, 
    "$.*" COLUMNS (
          rn FOR ORDINALITY,
          value VARCHAR(100) PATH '
Output (for your sample data):

Demo on dbfiddle

          )
    ) v ON n.rn = v.rn

Output (for your sample data):

Demo on dbfiddle

回复收藏 0 原文

~没有更多了~