MAPJSONEXTRACTOR
Extracts content of repeated JSON data objects, including nested maps, or data with an outer list of JSON elements. The USING PARAMETERS
phrase specifies optional parameters for the function. Empty input does not generate a Warning or Error.
Parameters
flatten_maps
|
BOOLEAN |
[Optional] Flattens sub-maps within the JSON data, separating map levels with a period ( Default value: |
flatten_arrays
|
BOOLEAN |
[Optional] Converts lists to sub-maps with integer keys. Lists are not flattened by default. Default value: |
reject_on_duplicate
|
BOOLEAN |
[Optional] Specifies whether to ignore duplicate records ( Default value: |
reject_on_empty_key
|
BOOLEAN |
[Optional] Rejects any row containing a key without a value ( Default value: |
omit_empty_keys
|
BOOLEAN |
[Optional] Omits any key from the load data that does not have a value ( Default value: |
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 Default value: none |
Examples
These examples use the following sample JSON data:
{ "id": "5001", "type": "None" } { "id": "5002", "type": "Glazed" } { "id": "5005", "type": "Sugar" } { "id": "5007", "type": "Powdered Sugar" } { "id": "5004", "type": "Maple" }
Save this example data as bake_single.json
, and load that file.
- Create a flex table,
flexjson
: - Use COPY to load the
bake_single.json
file with thefjsonparser
parser: - Create a columnar table,
coljson
, with an identity column (id
), ajson
column, and a column to hold a VMap, calledvmap
: - Use COPY to load the
bake_single.json
file into thecoljson
table, using themapjsonextractor
function: - Use the
maptostring
function for the flex tableflexjson
to output the__raw__
column contents as strings: - Use the
maptostring
function again, this time with thecoljson
table'svmap
column and compare the results. The element order differs:
=> CREATE flex table flexjson(); CREATE TABLE
=> COPY flexjson from '/home/dbadmin/data/bake_single.json' parser fjsonparser(); Rows Loaded ------------- 5 (1 row)
=> CREATE table coljson(id IDENTITY(1,1), json varchar(128), vmap long varbinary(10000)); CREATE TABLE
=> COPY coljson (json, vmap AS MapJSONExtractor(json)) FROM '/home/dbadmin/data/bake_single.json'; Rows Loaded ------------- 5 (1 row)
=> SELECT maptostring(__raw__) from flexjson limit 5; maptostring ----------------------------------------------------- { "id" : "5001", "type" : "None" } { "id" : "5002", "type" : "Glazed" } { "id" : "5005", "type" : "Sugar" } { "id" : "5007", "type" : "Powdered Sugar" } { "id" : "5004", "type" : "Maple" } (5 rows)
=> SELECT maptostring(vmap) from coljson limit 5; maptostring ----------------------------------------------------- { "id" : "5001", "type" : "None" } { "id" : "5002", "type" : "Glazed" } { "id" : "5004", "type" : "Maple" } { "id" : "5005", "type" : "Sugar" } { "id" : "5007", "type" : "Powdered Sugar" } (5 rows)