---
layout: default
title: JSON - Reference Manual - csvq
category: reference
---

# JSON

Some JSON data usage are suppored in csvq.
JSON data must be conforming to the [RFC8259](https://www.rfc-editor.org/info/rfc8259).

- Load data from a JSON file with the JSON_TABLE expression in [From Clause]({{ '/reference/select-query.html#from_clause' | relative_url }}).
- Load data from a JSON data from standard input with the [--json-query option]({{ '/reference/command.html#options' | relative_url }}).
- Export a result of a select query in JSON format with the [--format {JSON \| JSONH \| JSONA} option]({{ '/reference/command.html#options' | relative_url }}).
- Load a value from a JSON data using functions.
  1. [JSON_VALUE]({{ '/reference/string-functions.html#json_value' | relative_url }})
  2. [JSON_OBJECT]({{ '/reference/string-functions.html#json_object' | relative_url }})
  3. [JSON_AGG (Aggregate Function)]({{ '/reference/aggregate-functions.html#json_agg' | relative_url }})
  4. [JSON_AGG (Analytic Function)]({{ '/reference/analytic-functions.html#json_agg' | relative_url }})
- Load a row value from a JSON data using the [JSON_ROW]({{ '/reference/row-value.html' | relative_url }}) expression.


## JSON Query
{: #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

```sql
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
{: #encoding}

[--format option]({{ '/reference/command.html#options' | relative_url }}) and [JSON_OBJECT]({{ '/reference/string-functions.html#json_object' | relative_url }}) 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

```sql

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"}] |
-- +--------------------------------------------------------------------------------------------------+


```