CODEWITHSUNDEEP
mysqljsonnosqlmariadb
humano
humano

Reputation: 99

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

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

  1. 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'
  2. 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

Upvotes: 1

Views: 439

Answers (1)

Barbaros Özhan
Barbaros Özhan

Reputation: 65218

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

Upvotes: 1

Related Questions