The Ultimate Guide to High Availability with MariaDB

Comments - JSON_SET

6 days, 20 hours ago Phil S
Splicing arbitrary JSON data / non-scalar values

To splice arbitrary JSON data into the json_doc at the specified path, you must combine JSON_SET() with JSON_EXTRACT() like so:

JSON_SET(json_doc, path, JSON_EXTRACT(val, '$'))

Where val is a string of encoded JSON for any valid value (including scalars, because JSON_EXTRACT() handles both scalar and non-scalar values). E.g.:

JSON_SET(config, '$.options', JSON_EXTRACT('{"verbose":true, "type": "info"}', '$'))

(In MySQL you could CAST(val AS JSON) to do the same thing, but JSON is not a valid target type for CAST in MariaDB.)

Note that if you simply use JSON_SET(json_doc, path, '{...}') then you will instead set the value to a single string (containing the JSON-encoded data).

Note also that the call to JSON_EXTRACT() must directly provide the value. If you were to wrap it with another function, the end result will again just be a single string. So while JSON_EXTRACT() "Returns NULL if no paths match or if any of the arguments are NULL", you cannot use the following to provide a fallback value:

JSON_SET(json_doc, path, COALESCE(JSON_EXTRACT(val, '$'), '"invalid"'))

You could, however, use:

JSON_SET(json_doc, path, JSON_EXTRACT(COALESCE(JSON_EXTRACT(val, '$'), '"invalid"'), '$'))
 
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.