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;
New contributor
tech_learner is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.