Get data from JSON array in Mariadb

Question

I am trying to get records where end date is smaller than today's date and id =14. But I am getting 0 result. How to find records from array ?

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\"}]');

I have

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

This would be so much easier if you just had id and desc columns instead of a JSON value. — Nick
– Nick, Commented Feb 15, 2021 at 11:43

Barbaros Özhan · Accepted Answer · 2021-02-15 12:25:20Z

1

First of all, need to fix the format by converting $.[*] to $[*]. Then, consider using DATE_FORMAT() function due to the literals within the JSON value. Lastly, use JSON_SEARCH() function nested within JSON_VALUE() in order to extract the related values matching with the found index value for id and desc such as

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

edited Feb 15, 2021 at 12:25

answered Feb 15, 2021 at 9:48

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Get data from JSON array in Mariadb

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related