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.

Parameters

flatten_maps BOOLEAN

[Optional] Flattens sub-maps within the JSON data, separating map levels with a period (.).

Default value: true
flatten_arrays BOOLEAN

[Optional] Converts lists to sub-maps with integer keys. Lists are not flattened by default.

Default value: false
reject_on_duplicate BOOLEAN

[Optional] Specifies whether to ignore duplicate records (false), or to reject duplicates (true). In either case, the load continues.

Default value: false
reject_on_empty_key BOOLEAN

[Optional] Rejects any row containing a key without a value (reject_on_empty_key=true).

Default value: false
omit_empty_keys BOOLEAN

[Optional] Omits any key from the load data that does not have a value (omit_empty_keys=true).

Default value: false
record_terminator STRING

[Optional] 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, specify fjsonparser(record_terminator=E'\n'). If any invalid JSON records exist, parsing continues after the next record_terminator.

When you do not use a record_terminator, parsing ends at the first invalid JSON record.

Default value:no default value

reject_on_materialized_type_error BOOLEAN

[Optional] Rejects a data row that contains a materialized column value that cannot be coerced into a compatible data type ( reject_on_materialized_type_error=true.

Default value: false
start_point CHAR

[Optional] 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.

Default value: none
start_point_occurrence INTEGER

[Optional] 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 value: 1
suppress_nonalphanumeric_key_chars BOOLEAN

[Optional] Suppresses non-alphanumeric characters in JSON key values. The parser replaces these characters with an underscore (_) when this parameter is true.

Default value: false
key_separator CHAR

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

Default value: '.'

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