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.

Note: The function fails if the output size of the function is greater than 65000.

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

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

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.

  1. Create a flex table, flexjson
  2. => CREATE flex table flexjson();
    CREATE TABLE
    
  3. Use COPY to load the bake_single.json file with the fjsonparser parser: 
  4. => COPY flexjson from '/home/dbadmin/data/bake_single.json' parser fjsonparser();
     Rows Loaded
    -------------
               5
    (1 row)
    
  5. Create a columnar table, coljson, with an identity column (id), a json column, and a column to hold a VMap, called vmap
  6. => CREATE table coljson(id IDENTITY(1,1), json varchar(128), vmap long varbinary(10000));
    CREATE TABLE
    
  7. Use COPY to load the bake_single.json file into the coljson table, using the mapjsonextractor function:  
  8. => COPY coljson (json, vmap AS MapJSONExtractor(json)) FROM '/home/dbadmin/data/bake_single.json';
     Rows Loaded
    -------------
               5
    (1 row)
    
  9. Use the maptostring function for the flex table flexjson to output the __raw__ column contents as strings: 
  10. => 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)
    
  11. Use the maptostring function again, this time with the coljson table's vmap column and compare the results. The element order differs: 
  12. => 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