CODEWITHSUNDEEP
mysqlsqlarraysjson
Nguyn Xun Huy FU DN
Nguyn Xun Huy FU DN

Reputation: 21

How to update multi rows in MySQL from Array JSON

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

Upvotes: 2

Views: 1164

Answers (1)

buddemat
buddemat

Reputation: 5301

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.

Upvotes: 1

Related Questions