How to update multi rows in MySQL from Array JSON

Question

I have an JSON array that contains product table ids like: [2, 4, 5, 14]. How do I update the status of a Product to 0 if its ID is contained in the JSON array?

Here is the Product table:

id	name	color	price	status
2	Samsung S9	White	24000000	1
3	Oppo F5	Red	7000000	1
4	Macbook	Grey	5000000	1
5	MSI GT 900	Black	12000000	1
12	Iphone 11 Pro Max	Green	1800000	1
13	Nokia 6300	Blue	1100000	1
14	IPad Mini	White	2000000	1

I can't make comment bc I am new, but think this could help. [similar question](https://stackoverflow.com/questions/39906435/convert-json-array-in-mysql-to-rows) — Projekat Pr, Jul 03 '21 at 18:35
Join by JSON_CONTAINS or JSON_SEARCH. Or parse array with JSON_TABLE then join. — Akina, Jul 03 '21 at 19:28

buddemat · Answer 1 · 2021-07-04T21:40:50.607

You can use JSON_TABLE() (in MySQL > 8.0, scroll down for MySQL <= 5.7 solution).

You need to use the appropriate JSON selector:

Selector	Description
`$`	the current JSON document
`.key`	select value from the key
`[n]`	select element `n` from JSON array
`.*`	wildcard, all values of a JSON object, as an array
`[*]`	wildcard, all the values of a JSON array, as an array
`**`	wildcard, all paths that lead to a suffix

So, if @j = '[2, 4, 5, 14]' is your JSON array, then the query

SELECT * 
   FROM JSON_TABLE(@j, 
                   "$[*]" 
                   COLUMNS(Value INT PATH "$")
   ) data;

will get you the array contents as a table:

Value
2
4
5
14

You can use this subquery in an UPDATE to check whether your respective id is in it:

UPDATE tab 
   SET status = 0
 WHERE id in (SELECT * 
                FROM JSON_TABLE(@j, "$[*]" COLUMNS(Value INT PATH "$")) data
);

will get you the following:

id	name	color	price	status
2	Samsung S9	White	24000000	0
3	Oppo F5	Red	7000000	1
4	Macbook	Grey	5000000	0
5	MSI GT 900	Black	12000000	0
12	Iphone 11 Pro Max	Green	1800000	1
13	Nokia 6300	Blue	1100000	1
14	IPad Mini	White	2000000	0

In case your JSON looks differently, you need to change the PATH in the JSON_TABLE() call accordingly. For example:

SET @k = '{"id": [2, 4, 5, 14] }'
SELECT * FROM JSON_TABLE(@k, "$.id[*]" COLUMNS(Value INT PATH "$")) data;

or

SET @l = '{"outer": [{"id": 2} , {"id": 4}, {"id": 5}, {"id": 14}] }'
SELECT * FROM JSON_TABLE(@l, "$.outer[*]" COLUMNS(Value INT PATH "$.id")) data;

have the same result.

See this db<>fiddle.

For MySQL <= 5.7, JSON_TABLE() is not available, but you can achieve the same using JSON_CONTAINS(). Since JSON_CONTAINS() needs a JSON as argument, you need to CONVERT() or CAST() your INTEGER id to a CHAR:

UPDATE tab 
   SET status = 0
 WHERE JSON_CONTAINS(@j, CAST(id as CHAR(20)), '$');

See this db<>fiddle.

I tried your way but my query can execute and show error: 'You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column'. I tried with example MySQL <=5.7 — Nguyn Xun Huy FU DN, Jul 05 '21 at 12:36
It seems that MySQL does not recognize the use of the `id` in the `JSON_CONTAINS()`function as key column in the `WHERE` condition. One way to get around this is to disable safe update mode before running your query by `SET SQL_SAFE_UPDATES=0;` and re-enable it afterwards with `SET SQL_SAFE_UPDATES=1;`. See [db<>fiddle](https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=ac19243a22f2ce7366d29c98d7ef9f1e). — buddemat, Jul 05 '21 at 15:13

How to update multi rows in MySQL from Array JSON

1 Answers1