FJSONPARSER

Parses and loads a JSON file. This file can contain either repeated JSON data objects (including nested maps), or an outer list of JSON elements. For a flex table, the parser stores the JSON data in a single-value VMap. For a hybrid or columnar table, the parser loads data directly in any table column with a column name that matches a key in the JSON source data.

Syntax

FJSONPARSER ( [parameter‑name='value'[,…]] )

Parameters

flatten_maps

Boolean, specifies whether to flatten sub-maps within the JSON data, separating map levels with a period (.).

Default: true
flatten_arrays

Boolean, specifies whether to convert lists to sub-maps with integer keys. Lists are not flattened by default.

Default: false
reject_on_duplicate

Boolean, specifies whether to ignore duplicate records (false), or to reject duplicates (true). In either case, the load continues.

Default: false
reject_on_empty_key

Boolean, specifies whether to reject any row containing a key without a value.

Default: false
omit_empty_keys

Boolean, specifies whether to omit any key from the load data that does not have a value.

Default: false
record_terminator

When set, any invalid JSON records are skipped and parsing continues with the next record. Records must be terminated uniformly. For example, if your input file has JSON records terminated by newline characters, set this parameter to E'\n'). If any invalid JSON records exist, parsing continues after the next record_terminator.

When you omit this parameter, parsing ends at the first invalid JSON record.
reject_on_materialized_type_error

Boolean, specifies whether to reject a data row that contains a materialized column value that cannot be coerced into a compatible data type.

Default: false
start_point

String that specifies the name of a key in the JSON load data at which to begin parsing. The parser ignores all data before the start_point value. The value is loaded for each object in the file. The parser processes data after the first instance, and up to the second, ignoring any remaining data.

start_point_occurrence

Iinteger that indicates the nth occurrence of the value you specify with start_point. Use in conjunction with start_point when load data has multiple start values and you know the occurrence at which to begin parsing.

Default: 1
suppress_nonalphanumeric_key_chars

Boolean, specifies whether to suppress non-alphanumeric characters in JSON key values. The parser replaces these characters with an underscore (_) when this parameter is true.

Default: false
key_separator

Specifies a non-default character for the parser to use when concatenating key names.

Default: period (.)

Examples

Load JSON Data Without Optional Parameters

  1. Create a flex table, super, with two columns, age and name: 
    2. => create table super(age int, name varchar);
CREATE TABLE
  2. Enter values using the fjsonparser(), and query the results: 
    3. => copy super from stdin parser fjsonparser();
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> {"age": 5, "name": "Tim"}
>>  {"age": 3}
>>  {"name": "Fred"}
>>  {"name": "Bob", "age": 10}
>> \.
=> select * from super;
 age | name
-----+------
     | Fred
  10 | Bob
   5 | Tim
   3 |
(4 rows)

For other examples, see Loading JSON Data.

    See Also