Comments - JSON_TABLE

It doesn't seem possible to use this function with values that have double quote in them, at least not in 10.6.

SELECT * FROM JSON_TABLE( '[{\"text\":\"The book\'s title is
\"Lord of the Rings
\"\"}]', '$[*]' COLUMNS ( `text` VARCHAR(39) PATH '$.text' ) ) AS st

Returns: The book's title is \"Lord of the Rings Should be: The book's title is "Lord of the Rings"

It seems JSON_TABLE does not unquote JSON strings when producing a VARCHAR column.

You can achieve this using the JSON type for the column and explicitly unquoting using JSON_UNQUOTE :

SELECT JSON_UNQUOTE(text) FROM JSON_TABLE( '[{"text":"The book''s title is \\"Lord of the Rings\\""}]', '$[*]' COLUMNS ( `text` JSON PATH '$.text' ) ) AS st

Result :

The book's title is "Lord of the Rings"

I have the following JSON in MariaDB/MySQL:

[{"uid": 5}, {"uid": 6}, {"uid": 7}]

TABLE> user_pst_tb

COLUMNS >

pst_id | pst_liked_by (This is the JSON Column)

------------------------

30 | [{"uid": 5}, {"uid": 6}, {"uid": 7}]

i want to use any method to remove {"uid": 6} alone on pst_id = 30, but i cannot find how to formulate the path. I thought of this:

UPDATE user_pst_tb SET `pst_liked_by` = JSON_REMOVE( `pst_liked_by`, JSON_UNQUOTE( REPLACE( JSON_SEARCH( `pst_liked_by`, 'one', '6', null, '$.uid' ) , '.uid' , '' ) ) ) WHERE pst_id = 30;

for some reason the MariaDB and MySQL docs does not have such examples. Any help is appreciated.

I have also tried:

UPDATE user_pst_tb SET `pst_liked_by`= JSON_REMOVE(`pst_liked_by`, JSON_UNQUOTE( JSON_SEARCH(`pst_liked_by`, 'one','{"uid": 6}') )) WHERE `pst_id` = 30;

The second query clears all the JSON data sadly

Please HELP

how to make the return like this

+-------+------+

+-------+------+

+-------+------+

You tantalizingly dangled a nested JSON structure in front of us, then didn't use it!

Is there any support to deal with nested structures in a reasonable way?

Yes there is! The documentation is still a work in progress and should be finalized soon. In the meantime, you can watch Sergei Petrunia's presentation at FOSDEM 2021 as it covers most functionality. https://fosdem.org/2021/schedule/event/mariadb_json/