CODEWITHSUNDEEP
jsonmariadb
kreya
kreya

Reputation: 1229

Get data from JSON array in Mariadb

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'

Upvotes: 0

Views: 154

Answers (1)

Barbaros &#214;zhan
Barbaros &#214;zhan

Reputation: 65228

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

Upvotes: 1

Related Questions