Extracting and Converting JSON Values

Extracting JSON Values

subvalue_mapping specifies which subvalues are extracted and the column or variable to which each one is assigned.

LOAD DATA uses the ::-separated list of keys in a subvalue_path to perform successive key lookups in nested JSON objects, as if applying the :: SQL operator. Unlike the :: operator, subvalue_path may not be used to extract an element of a JSON array. The path % refers to the entire JSON value being processed. Leading %:: may be omitted from paths which are otherwise non-empty.

If a path can’t be found in an input JSON value, then if the containing element of subvalue_mapping has a DEFAULT clause, its literal_expr will be assigned; otherwise, LOAD DATA will terminate with an error.

Path components containing whitespace or punctuation must be surrounded by backticks. For example, the paths %::`a.a`::b and `a.a`::b will both extract 1 from the input object {"a.a":{"b":1},"c":2}.

Array elements may be indirectly extracted by applying JSON_EXTRACT_<type> in a SET clause.

Converting JSON Values

Before assignment or set clause evaluation, the JSON value extracted according to a subvalue_path is converted to a binary collation SQL string whose value depends on the extracted JSON type as follows:

Conversion is not recursive. So, for example, true is not converted to "1" when it is a subvalue of an object which is being extracted whole.