时间戳

首页 > 解决方案 > 从 Mariadb 中的 JSON 数组获取数据

json - 从 Mariadb 中的 JSON 数组获取数据

问题描述

我正在尝试获取结束日期小于今天日期且 id = 14 的记录。但我得到 0 结果。如何从数组中查找记录？

CREATE TABLE `test_udc` (
  `id` int(10) NOT NULL,
  `test_udc` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL
) ;



INSERT INTO `test_udc` (`id`, `test_udc`) VALUES
(1, '[{\"id\":7,\"desc\":\"Be Alert\"},{\"id\":8,\"desc\":\"Dog - Phone Ahead\"},{\"id\":14,\"desc\":\"Self-Isolating CV19\",\"start_date\":\"04-02-2021\",\"end_date\":\"14-02-2021\"}]'),
(2, '[{\"id\":7,\"desc\":\"Be Alert\"},{\"id\":14,\"desc\":\"Self-Isolating CV19\",\"start_date\":\"04-02-2021\",\"end_date\":\"16-02-2021\"}]');

我有

SELECT * FROM `test_udc` where json_value(`test_udc`,'$.[*].id')='14' AND json_value(`test_udc`,'$.[*].end_date')<'2021-02-15'

标签： jsonmariadb

解决方案

首先，需要通过转换$.[*]为$[*]. DATE_FORMAT()然后，由于 JSON 值中的文字，考虑使用函数。最后，使用JSON_SEARCH()嵌套在其中的函数JSON_VALUE()来提取与找到的索引值匹配的相关值id，desc例如

SET @id = 14;

WITH t AS
(
 SELECT JSON_UNQUOTE(JSON_SEARCH(`test_udc`, 'one', @id)) AS elm,
        t.* 
   FROM `test_udc` AS t
  WHERE STR_TO_DATE(JSON_VALUE(`test_udc`,'$[*].end_date'),'%d-%m-%Y') < CURDATE()
)
SELECT JSON_VALUE(`test_udc`,elm) AS elm_id,
       JSON_VALUE(`test_udc`,REPLACE(elm,'id','desc')) AS elm_desc
  FROM t ;

elm_id  elm_desc
------  -------------------
14      Self-Isolating CV19

Demo

推荐阅读