MAPJSONEXTRACTOR

Extracts content of repeated JSON data objects,, including nested maps, or data with an outer list of JSON elements.

Produces a VMap of key/value pairs from an input JSON string. You typically use this function with COPY to populate a VMap column from another string column containing the JSON.

Empty input does not generate warnings or errors.

Syntax

MAPJSONEXTRACTOR (record-value [ USING PARAMETERS param=value[,...] ])

Arguments

record-value
String containing a JSON or delimited format record on which to apply the expression.

Parameters

flatten_maps (Boolean)
If true, flatten sub-maps within the JSON data, separating map levels with periods (.).

Default: true

flatten_arrays (Boolean)
If true, convert lists to sub-maps with integer keys. Lists are not flattened by default.

Default value: false

reject_on_duplicate (Boolean)
If true, reject duplicates. If false, ignore duplicate records. In either case, loading is unaffected.

Default: false

reject_on_empty_key (Boolean)
If true, reject any row that contains a key without a value.

Default: false

omit_empty_keys (Boolean)
If true, omit any key from the data without a value.

Default: false

start_point (String)
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 parser processes data after the first instance, and up to the second, ignoring any remaining data.

Default: none

Examples

This example uses the following sample JSON data in a file named bakery.json:

{ "id": "5001", "type": "None" }
{ "id": "5002", "type": "Glazed" }
{ "id": "5005", "type": "Sugar" }
{ "id": "5007", "type": "Powdered Sugar" }
{ "id": "5004", "type": "Maple" }

In addition to loading this data as a string, you can load it as a VMap using this function:

=> CREATE TABLE bakery(id IDENTITY(1,1), json VARCHAR(128), vmap LONG VARBINARY(10000));
CREATE TABLE

=> COPY bakery (json, vmap AS MapJSONExtractor(json)) 
   FROM '/home/dbadmin/data/bakery.json';
 Rows Loaded
-------------
           5
(1 row)

You can now use MAPTOSTRING to show the values from the VMap:

=> SELECT MAPTOSTRING(vmap) FROM bakery 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)

If you load the data into a flex table, you must qualify the filler column to disambiguate it from possible fields in the VMap. Use the following syntax to refer to a filler column when loading into a flex table:

=> CREATE FLEX TABLE bakery2(id IDENTITY(1,1), vmap LONG VARBINARY(10000));
CREATE TABLE

=> COPY bakery2 (json FILLER VARCHAR(128), 
                 vmap AS MapJSONExtractor("*FILLER*".json))
   FROM '/home/dbadmin/data/bakery.json';
 Rows Loaded
-------------
           5
(1 row)

If you call MAPTOSTRING on the __raw__ column in this flex table, the order of elements might be different from the previous example, but the output is otherwise the same.

See also