MAPJSONEXTRACTOR

Extracts content of repeated JSON data objects,, including nested maps, or data with an outer list of JSON elements. You can set one or more optional parameters to control the extraction process.

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, flatten sub-maps within the JSON data, separating map levels with a period (.).

Default: true

flatten_arrays

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

Default value: false

reject_on_duplicate

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

Default: false

reject_on_empty_key

Boolean, reject any row that contains a key without a value.

Default: false

omit_empty_keys

Boolean, omit any key from the load data without a value.

Default: false

start_point

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

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.

1. Create a flex table, flexjson:

   => CREATE FLEX TABLE flexjson();
   CREATE TABLE
   

2. Use COPY to load the bake_single.json file with the fjsonparser parser:

   => COPY flexjson FROM '/home/dbadmin/data/bake_single.json' parser fjsonparser();
    Rows Loaded
   -------------
              5
   (1 row)
   

3. Create a columnar table, coljson, with an IDENTITY column (id), a json column, and a column to hold a VMap, called vmap:

   => CREATE TABLE coljson(id IDENTITY(1,1), json varchar(128), vmap long varbinary(10000));
   CREATE TABLE
   

4. Use COPY to load the bake_single.json file into the coljson table, using MAPJSONEXTRACTOR:

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

5. Use the MAPTOSTRING function for the flex table flexjson to output the __raw__ column contents as strings:

   => 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)
   

6. Use MAPTOSTRING again, this time with the coljson table's vmap column and compare the results. The element order differs:

   => 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)
   

See also

• MAPDELIMITEDEXTRACTOR
• MAPREGEXEXTRACTOR