CODEWITHSUNDEEP
mysqlsqljsonmariadb
Guss
Guss

Reputation: 32414

How to insert an array value to an existing JSON document?

Given a column containing a JSON document, I can use JSON_SET() to set a key in the JSON document to a value. Supported values are null, true, false, numbers and strings; but I can't figure out how to set an array.

The example in the documentation (for JSON_INSERT(), but it works the same) weirdly shows how a naive user might try and fail to set an array value, but kind of lampshades it; if you look closely you will find that the array was converted to a string.

I figured out a workaround where you first use:

JSON_SET(col, '$.field', "first value")
-- {"field": "first value"}

and then:

JSON_ARRAY_APPEND(col, '$.field', "second value")
-- {"field": ["first value", "second value"]}

But there are all kinds of problems with that, not the least of it is that it can't be used to set an array with less than 2 values.

Upvotes: 2

Views: 1717

Answers (2)

phils
phils

Reputation: 73390

If the data you want to set is also in the form of a JSON-encoded string, you can use JSON_EXTRACT() with JSON_SET() in MariaDB, like so:

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

Where value 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(value 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"'), '$'))

Upvotes: 0

Salman Arshad
Salman Arshad

Reputation: 272426

JSON_INSERT/JSON_REPLACE/JSON_SET will happily accept a JSON document as the value:

SELECT JSON_INSERT('{"foo": "bar"}', '$.new', JSON_ARRAY('one', 'two', 'three'))
/*
{
    "foo": "bar",
    "new": ["one", "two", "three"]
}
*/

Note that:

  • JSON_SET() replaces existing values and adds nonexisting values.
  • JSON_INSERT() inserts values without replacing existing values.
  • JSON_REPLACE() replaces only existing values.

If you want to append values to an array that may/may not already exist then chain the functions in this order:

SELECT JSON_ARRAY_APPEND(JSON_INSERT('{"foo": "bar"}', '$.new', JSON_ARRAY()), '$.new', 'four')
/*
{
    "foo": "bar",
    "new": ["four"]
}
*/
SELECT JSON_ARRAY_APPEND(JSON_INSERT('{"foo": "bar", "new": ["one", "two", "three"]}', '$.new', JSON_ARRAY()), '$.new', 'four')
/*
{
    "foo": "bar",
    "new": ["one", "two", "three", "four"]
}
*/

Upvotes: 2

Related Questions