在mysql嵌套的JSON数据中获取元素值

发布于 2025-01-21 11:22:49 字数 656 浏览 1 评论 0原文

从JSON字段中，获得<> chip child> Child1dob1 的最后一个事件的值的正确方法是从具有下面的数据结构的JSON字段中，

{
  "data": {
    "data": {
      "data": {
        "child1Dob1": "Andy"
      },
      "child1Dob1": "Bob"
    },
    "child1Dob1": "Rick"
  },
  "child1Dob1": "Ajax"
}

以下查询是类似问题但是我的价值为无效，所以显然我缺少一些东西。

SELECT JSON_EXTRACT(`containerValue`,CONCAT("$.data[",JSON_LENGTH(`containerValue` ->> '$.data')-1,"]")) from myTable where containerKey = 'theContainer';

原文

What would be the right way of getting Ajax, i.e. the value for the last occurence for key child1Dob1, from a json field that has a data structure that looks like the below,

{
  "data": {
    "data": {
      "data": {
        "child1Dob1": "Andy"
      },
      "child1Dob1": "Bob"
    },
    "child1Dob1": "Rick"
  },
  "child1Dob1": "Ajax"
}

The below query was an attempt from a similar question but i am getting a null value, so obviously i am missing something.

SELECT JSON_EXTRACT(`containerValue`,CONCAT("$.data[",JSON_LENGTH(`containerValue` ->> '$.data')-1,"]")) from myTable where containerKey = 'theContainer';

分享到QQ

分享到微博

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

发布评论

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

像极了他 2025-01-28 11:22:50

对于创建表测试（数据JSON）：

WITH RECURSIVE 
cte AS (
  SELECT data, data -> '$.data' subdata
  FROM test
  UNION ALL
  SELECT subdata, subdata -> '$.data' 
  FROM cte
  WHERE subdata IS NOT NULL
)
SELECT data ->> '$.child1Dob1'
FROM cte
WHERE subdata IS NULL;

For CREATE TABLE test (data JSON):

WITH RECURSIVE 
cte AS (
  SELECT data, data -> '$.data' subdata
  FROM test
  UNION ALL
  SELECT subdata, subdata -> '$.data' 
  FROM cte
  WHERE subdata IS NOT NULL
)
SELECT data ->> '$.child1Dob1'
FROM cte
WHERE subdata IS NULL;

回复收藏 0 原文

~没有更多了~