时间戳

首页 > 解决方案 > Mariadb 查询和提取 JSON 数据

json - Mariadb 查询和提取 JSON 数据

问题描述

我有一个 MaraiDB (10.2.14) 数据库，其中包含一个表，其中存储了一列 JSON 数据。

我很困惑如何从该列中提取数据。

示例数据

SELECT 1 AS ID
     , '[{"name":"x","score":2},{"name":"y", "score":8},{"name":"z","score":3}]' AS REPLY
UNION ALL
SELECT 2 AS ID
     , '[{"name":"x","score":5},{"name":"y", "score":4},{"name":"z","score":3}]' AS REPLY
UNION ALL
SELECT 3 AS ID
     , '[{"name":"x","score":2},{"name":"y", "score":2},{"name":"z","score":6}]' AS REPLY
UNION ALL
SELECT 4 AS ID
     , '[{"name":"x","score":5},{"name":"y", "score":8},{"name":"z","score":6}]' AS REPLY

那么我如何找到所有具有“name”：“x”和“score”：5的条目。此外，我需要获取该条目的“名称”：“y”的“分数”值。

我目前的肮脏方法是

WITH JT1 AS (
        SELECT 1 AS ID
             , '[{"name":"x","score":2},{"name":"y", "score":8},{"name":"z","score":3}]' AS REPLY
        UNION ALL
        SELECT 2 AS ID
             , '[{"name":"x","score":5},{"name":"y", "score":4},{"name":"z","score":3}]' AS REPLY
        UNION ALL
        SELECT 3 AS ID
             , '[{"name":"x","score":2},{"name":"y", "score":2},{"name":"z","score":6}]' AS REPLY
        UNION ALL
        SELECT 4 AS ID
             , '[{"name":"x","score":5},{"name":"y", "score":8},{"name":"z","score":6}]' AS REPLY
)
SELECT ID
     , REGEXP_REPLACE(
         REGEXP_REPLACE( EXTRACTED, '^.*"y",\\s', '')
       , '[,\\]].*$', '') AS Y
     , EXTRACTED
FROM (
        SELECT ID
             , JSON_EXTRACT(REPLY, '$[*].name','$[*].score') EXTRACTED
        FROM JT1
) JT2
WHERE EXTRACTED RLIKE '"x", 5\\b'
;

所以我首先提取“名称”和“分数”，这给了我像["x", 5, "y", 4, "z", 3]. 有了它，我做了一些讨厌的 REGEXP 搜索和替换。

我觉得必须有更好的方法。

我尝试使用 COLUMN_CREATE，但“COLUMN_CREATE”似乎无法接受来自 JSON_EXTRACT 的结果作为输入。现在我想起来了，这似乎是合乎逻辑的，因为这里的“名称”和“分数”是正确排序的，但我可以确定它总是那个顺序吗？

谁能给我一个提示如何更好地做到这一点？

标签： jsonmariadb

解决方案

如果我了解您的需求，类似以下的查询可能会有用：

WITH `JT1` AS (
  SELECT 1 AS `ID`
  , '[{"name":"x","score":2},{"name":"y", "score":8},{"name":"z","score":3}]' AS `REPLY`
  UNION ALL
  SELECT 2 AS `ID`
  , '[{"name":"x","score":5},{"name":"y", "score":4},{"name":"z","score":3}]' AS `REPLY`
  UNION ALL
  SELECT 3 AS `ID`
  , '[{"name":"x","score":2},{"name":"y", "score":2},{"name":"z","score":6}]' AS `REPLY`
  UNION ALL
  SELECT 4 AS `ID`
  , '[{"name":"x","score":5},{"name":"y", "score":8},{"name":"z","score":6}]' AS `REPLY`
)
SELECT
  `ID`,
  `REPLY`,
  JSON_VALUE(
    `REPLY`,
    JSON_UNQUOTE(
      REPLACE(
        JSON_SEARCH(`REPLY`, 'one', 'y', NULL, '$[*].name'),
        'name',
        'score'
      )
    )
  ) `"name":"y"`
FROM
  `JT1`
WHERE
  JSON_VALUE(
    `REPLY`,
    JSON_UNQUOTE(
      REPLACE(
        JSON_SEARCH(`REPLY`, 'one', 'x', NULL, '$[*].name'),
        'name',
        'score'
      )
    )
  ) = 5;

请参阅dbfiddle。

推荐阅读