SQL query to update JSON field value in MySQL

Question

Using MySQL, I need to update record where there is a null value in a field of type json. For example,

Table 1

Column A	Column B
Cell 1	[{"id":1, "name":"abc"},{"id":2, "name":"xyz"},{"id":3, "name":null}]
Cell 2	[{"id":4, "name":"test 1"},{"id":5, "name":null}]

Table 2

id	name
1	abc
2	xyz
3	null
4	test 1
5	null

I wrote this query to first retrieve the records where null value is stored in name key

SELECT *
FROM table 1
WHERE JSON_CONTAINS(column B, '{"name": null}', '$');

Now I need to update the records using an SQL query to delete the object from the array in table 1 column B. Table 1 should like this.

Table 1

Column A	Column B
Cell 1	[{"id":1, "name":"abc"},{"id":2, "name":"xyz"}]
Cell 2	[{"id":4, "name":"test 1"}]

How can I achieve this without writing stored procedures?

I tried updating one record using this query

UPDATE table 1
SET column_B = JSON_ARRAY(
    JSON_OBJECT(
       "id", JSON_UNQUOTE(JSON_EXTRACT(column_B, '$[0].id')), 
       "name", JSON_UNQUOTE(JSON_EXTRACT(injuries, '$[0].name'))
    ),
)
WHERE id = 1;

Answer 1

There is a JSON function JSON_TABLE, which used to convert each JSON array into number of JSON Objects(rows) contains in that JSON Array. Let if a JSON array contact n JSON object, then it'll create n rows for Each Object which exists in that JSON array.

For Example:
Lets, my Input is:

I want to break as follows:

set @jsonArray = JSON_ARRAY(
     JSON_OBJECT('key', 1, 'value', 'Data1'), 
     JSON_OBJECT('key', 2, 'value', 'Data2'), 
     JSON_OBJECT('key', 3, 'value', 'Data3')
);

select * 
from JSON_TABLE(
    @jsArray, '$[*]' COLUMNS (
        split_json_array JSON PATH '$'
    )
) AS JT

Similar way we need to convert all 'Column B' JSON array value into respective format. Then we have to apply condition, split_json_array->>'$.name' = 'null' filter 'Column B' JSON Array elements, which name is exits, and then need to merge those data.

So, respective query is:

WITH Base_Table_1 AS (
    select * from 
        Table_1,
        JSON_TABLE(column_B, '$[*]' COLUMNS (
            split_json_array JSON PATH '$'
        )) AS jt
),
filter_Table_1 AS (
    SELECT
        column_A,
        JSON_ARRAYAGG(split_json_array) AS column_B_filtered_data
    from Base_Table_1
    where split_json_array->>'$.name' != 'null'
    group by column_A
)
Select * from filter_Table_1;

Output:

Next update your Table_1 table column_B value from filter_Table_1 table:

UPDATE Table_1
SET column_B = (
    select filter_Table_1.column_B_filtered_data
    From filter_Table_1
    WHERE Table_1.column_A = filter_Table_1.column_A
);

So, latest Table_1 is showing as:

select * from Table_1;

Sample code for live testing: myCompiler

Needs lots of research & investigation for this and I learn something new related to MySQL JSON Data handling.

I hope your issue will resolve.
Thank you for this question.