JSON

Some JSON data usage are suppored in csvq. JSON data must be conforming to the RFC8259.

Load data from a JSON file with the JSON_TABLE expression in From Clause.
Load data from a JSON data from standard input with the –json-query option.
Export a result of a select query in JSON format with the –format {JSON | JSONH | JSONA} option.
Load a value from a JSON data using functions.
Load a row value from a JSON data using the JSON_ROW expression.

JSON Query

Elements

Value Identifier: A value identifier is used to represent an object member.
AN identifier is a word starting with any unicode letter or a Low Line(U+005F ‘_’) and followed by a character string that contains any unicode letters, any digits or Low Lines(U+005F ‘_’). You can use most character strings as an identifier by enclosing in Back Quotes(U+0060 `), Single Quotes(U+0027 ‘) or Double Quotes(U+0022 “). Quotation Marks are escaped by backslashes.
Array Index: Number of json array elements starting with 0.
Value Separator: A period(U+002E ‘.’) is used to separate values and that represents a child object.
Array: Square Brackets(U+005B ‘[’, U+005D ‘]’) are used to represent json array.
Object Array: Curly Brackets(U+007B ‘{‘, U+007D ‘}’) are used to repsesent json array of objects.

Expressions

value
  : {object_member | array_element}
  | value[. value ...]

object_member
  : value_identifier

array_element
  : [index]

json_array
  : []
  
object_array
  : {[field [, field...]]}

field
  : field_name
  | field_name as alias

object_member and array_element returns null if the element does not exist.

json_array format a json data in an array. object_array format a json data in an array that’s all elements are objects. json_array and array_of_objects cause an error if the element does not exist or fails to be converted.

Examples

VAR @json := '[
  {
    "id": 1,
    "first name": "Louis",
    "last name": "Washington",
    "authority": [1, 2, 15],
  },
  {
    "id": 2,
    "first name": "Sean",
    "last name": "Burton",
    "authority": [1, 3],
    "email": "sean@example.com"
  }
]'


SELECT JSON_VALUE('[1].`first name`', @json);
-- Result: String('Sean')

SELECT JSON_VALUE('[].id', @json);
-- Result: String('[1, 2]')

SELECT JSON_VALUE('{id, `first name` as name}', @json);
-- Result: String('[{"id":1,"name":"Louis"},{"id":2,"name":"Sean"}]')

SELECT * FROM JSON_TABLE{'{}', @json};
-- +----+------------+------------+-----------+------------------+
-- | id | first name | last name  | authority | email            |
-- +----+------------+------------+-----------+------------------+
-- |  1 | Louis      | Washington | [1,2,15]  |             NULL |
-- |  2 | Sean       | Burton     | [1,3]     | sean@example.com |
-- +----+------------+------------+-----------+------------------+

SELECT * FROM users WHERE id IN JSON_ROW('[].id', @json);

ENCODING

–format option and JSON_OBJECT function generate a json data from a view.

A record in a view will be converted to a json object. Object member names are generated from field names in the view. A period(U+002E ‘.’) in a column name is used to separate values and that represents a child object.

Examples

SELECT * FROM json_table;
-- +----+------------+------------+-----------+------------------+
| id | name.first | name.last  | authority | email            |
-- +----+------------+------------+-----------+------------------+
-- |  1 | Louis      | Washington | [1,2,15]  |             NULL |
-- |  2 | Sean       | Burton     | [1,3]     | sean@example.com |
-- +----+------------+------------+-----------+------------------+

SELECT JSON_OBJECT() FROM json_table;
-- +--------------------------------------------------------------------------------------------------+
-- | JSON_OBJECT()                                                                                    |
-- +--------------------------------------------------------------------------------------------------+
-- | {"id":"1","name":{"first":"Louis","last":"Washington"},"authority":[1,2,15],"email":null}        |
-- | {"id":"2","name":{"first":"Sean","last":"Burton"},"authority":[1,3],"email":"sean@example.com"}] |
-- +--------------------------------------------------------------------------------------------------+