How to find and delete elements within a json array in mariadb or mysql

Question

I have this json in the 'data' field of a mariadb table and I need:

know if a value exists within eg: TN-17170 ": {" id_fact ":" 6 "} SELECT JSON_EXTRACT(data, '$**.TN-17170.id_fact') as exist from fact WHERE id ='6' But this shows something like ['6'], I would like to just get '6'
In another sentence, if the value exists, delete it. I try with UPDATE `fact` set datos= JSON_REMOVE(data, '$.datos.TN-17170') WHERE id ='6' , but I can't.

Please help me organize the queries, there is not much practical documentation about the use of json in mariadb and I want to use these functions.

CREATE TABLE `fact` (
  `id` int(20) NOT NULL,
  `data` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_spanish2_ci;

INSERT INTO `fact` (`id`, `data`) VALUES
(6, '{\"id\": \"23\", \"datos\": [{\"TN-17170\": {\"id_fact\": \"6\"}}, {\"TN-17171\": {\"id_fact\": \"6\"}}, {\"TN-17173\": {\"id_fact\": \"6\"}}, {\"TN-17127\": {\"id_fact\": \"6\"}}, {\"TN-17177\": {\"id_fact\": \"6\"}}, {\"TN-17397\": {\"id_fact\": \"6\"}}]}');

SELECT JSON_EXTRACT(data, '$**.TN-17170.id_fact') as exist from fact WHERE id ='6' ;

SELECT * FROM fact ;

UPDATE `fact` set data= JSON_REMOVE(data, '$.datos.TN-17170') WHERE id ='6';
SELECT * FROM fact ;

db example

I've not yet used the JSON function, don't have the version. (coming soon though) ... but MariaDB does have documentation on the various JSON functions, some with a video too. I may be able to give a try at a db fiddle as well, if you can add the table structure and the complete row of data to the question. — Paul T.
– Paul T., Commented Sep 17, 2020 at 0:03
Thanks, I have already edited the question and put the example dbfiddle.uk/… — humano
– humano, Commented Sep 17, 2020 at 12:21
Ok, thanks for that. I've updated the fiddle to break up the various queries for checking. I'm still only getting the ["6"] as well, and no success so far with the json_remove function either. May be much later today before I can try other things. — Paul T.
– Paul T., Commented Sep 17, 2020 at 15:08

Barbaros Özhan · Accepted Answer · 2020-09-17 18:23:05Z

1

You need to specify an index within square brackets(0 in this case) for pattern of JSON_EXTRACT() function in order to extract the content of an array :

SELECT JSON_EXTRACT(data, '$.datos[0].TN-17170.id_fact') AS exist 
  FROM `fact` 
 WHERE id ='6';

and this can also be used within JSON_REMOVE() function :

UPDATE `fact` 
   SET data= JSON_REMOVE(data, '$.datos[0].TN-17170.id_fact') 
 WHERE id ='6';

Demo

answered Sep 17, 2020 at 18:23

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

How to find and delete elements within a json array in mariadb or mysql

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