github.com/mithrandie/csvq@v1.18.1/docs/_posts/2006-01-02-json.md (about)

     1  ---
     2  layout: default
     3  title: JSON - Reference Manual - csvq
     4  category: reference
     5  ---
     6  
     7  # JSON
     8  
     9  Some JSON data usage are suppored in csvq.
    10  JSON data must be conforming to the [RFC8259](https://www.rfc-editor.org/info/rfc8259).
    11  
    12  - Load data from a JSON file with the JSON_TABLE expression in [From Clause]({{ '/reference/select-query.html#from_clause' | relative_url }}).
    13  - Load data from a JSON data from standard input with the [--json-query option]({{ '/reference/command.html#options' | relative_url }}).
    14  - Export a result of a select query in JSON format with the [--format {JSON \| JSONH \| JSONA} option]({{ '/reference/command.html#options' | relative_url }}).
    15  - Load a value from a JSON data using functions.
    16    1. [JSON_VALUE]({{ '/reference/string-functions.html#json_value' | relative_url }})
    17    2. [JSON_OBJECT]({{ '/reference/string-functions.html#json_object' | relative_url }})
    18    3. [JSON_AGG (Aggregate Function)]({{ '/reference/aggregate-functions.html#json_agg' | relative_url }})
    19    4. [JSON_AGG (Analytic Function)]({{ '/reference/analytic-functions.html#json_agg' | relative_url }})
    20  - Load a row value from a JSON data using the [JSON_ROW]({{ '/reference/row-value.html' | relative_url }}) expression.
    21  
    22  
    23  ## JSON Query
    24  {: #query}
    25  
    26  ### Elements
    27  
    28  Value Identifier
    29  : A value identifier is used to represent an object member.
    30  
    31    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 '\_').
    32    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 ").
    33    Quotation Marks are escaped by backslashes.
    34  
    35  Array Index
    36  : Number of json array elements starting with 0.
    37  
    38  Value Separator
    39  : A period(U+002E '.') is used to separate values and that represents a child object.
    40  
    41  Array
    42  : Square Brackets(U+005B '\[', U+005D '\]') are used to represent json array.
    43  
    44  Object Array 
    45  : Curly Brackets(U+007B '{', U+007D '}') are used to repsesent json array of objects.
    46  
    47  
    48  ### Expressions
    49  
    50  ```
    51  value
    52    : {object_member | array_element}
    53    | value[. value ...]
    54  
    55  object_member
    56    : value_identifier
    57  
    58  array_element
    59    : [index]
    60  
    61  json_array
    62    : []
    63    
    64  object_array
    65    : {[field [, field...]]}
    66  
    67  field
    68    : field_name
    69    | field_name as alias
    70  ```
    71  
    72  _object_member_ and _array_element_ returns null if the element does not exist.
    73  
    74  _json_array_ format a json data in an array.
    75  _object_array_ format a json data in an array that's all elements are objects.
    76  _json_array_ and _array_of_objects_ cause an error if the element does not exist or fails to be converted.  
    77  
    78  ### Examples
    79  
    80  ```sql
    81  VAR @json := '[
    82    {
    83      "id": 1,
    84      "first name": "Louis",
    85      "last name": "Washington",
    86      "authority": [1, 2, 15],
    87    },
    88    {
    89      "id": 2,
    90      "first name": "Sean",
    91      "last name": "Burton",
    92      "authority": [1, 3],
    93      "email": "sean@example.com"
    94    }
    95  ]'
    96  
    97  
    98  SELECT JSON_VALUE('[1].`first name`', @json);
    99  -- Result: String('Sean')
   100  
   101  SELECT JSON_VALUE('[].id', @json);
   102  -- Result: String('[1, 2]')
   103  
   104  SELECT JSON_VALUE('{id, `first name` as name}', @json);
   105  -- Result: String('[{"id":1,"name":"Louis"},{"id":2,"name":"Sean"}]')
   106  
   107  SELECT * FROM JSON_TABLE{'{}', @json};
   108  -- +----+------------+------------+-----------+------------------+
   109  -- | id | first name | last name  | authority | email            |
   110  -- +----+------------+------------+-----------+------------------+
   111  -- |  1 | Louis      | Washington | [1,2,15]  |             NULL |
   112  -- |  2 | Sean       | Burton     | [1,3]     | sean@example.com |
   113  -- +----+------------+------------+-----------+------------------+
   114  
   115  SELECT * FROM users WHERE id IN JSON_ROW('[].id', @json);
   116  
   117  ```
   118  
   119  ## ENCODING
   120  {: #encoding}
   121  
   122  [--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.
   123  
   124  A record in a view will be converted to a json object.
   125  Object member names are generated from field names in the view.
   126  A period(U+002E '.') in a column name is used to separate values and that represents a child object.
   127  
   128  
   129  ### Examples
   130  
   131  ```sql
   132  
   133  SELECT * FROM json_table;
   134  -- +----+------------+------------+-----------+------------------+
   135  | id | name.first | name.last  | authority | email            |
   136  -- +----+------------+------------+-----------+------------------+
   137  -- |  1 | Louis      | Washington | [1,2,15]  |             NULL |
   138  -- |  2 | Sean       | Burton     | [1,3]     | sean@example.com |
   139  -- +----+------------+------------+-----------+------------------+
   140  
   141  SELECT JSON_OBJECT() FROM json_table;
   142  -- +--------------------------------------------------------------------------------------------------+
   143  -- | JSON_OBJECT()                                                                                    |
   144  -- +--------------------------------------------------------------------------------------------------+
   145  -- | {"id":"1","name":{"first":"Louis","last":"Washington"},"authority":[1,2,15],"email":null}        |
   146  -- | {"id":"2","name":{"first":"Sean","last":"Burton"},"authority":[1,3],"email":"sean@example.com"}] |
   147  -- +--------------------------------------------------------------------------------------------------+
   148  
   149  
   150  ```