MAPLOOKUP

Returns single-key values from VMAP data. This scalar function returns a LONG VARCHAR, with values, or NULL if the virtual column does not have a value.

Using maplookup is case insensitive to virtual column names. To avoid loading same-name values, set the fjsonparser parser reject_on_duplicate parameter to true when data loading.

You can control the behavior for non-scalar values in a VMAP (like arrays), when loading data with the fjsonparser or favroparser parsers and its flatten-arrays argument. See Loading JSON Data and the FJSONPARSER reference.

For information about using maplookup() to access nested JSON data, see Querying Nested Data.

Syntax

MAPLOOKUP (VMap-data, 'virtual-column-name' [USING PARAMETERS [case_sensitive={false | true}] [, buffer_size=n] ] )

Parameters

buffer_size

case_sensitive

(... USING PARAMETERS case_sensitive=true)

Examples

This example returns the values of one virtual column, user.location:

=> SELECT MAPLOOKUP(__raw__, 'user.location') FROM darkdata ORDER BY 1;
 maplookup
-----------
 Chile
 Nesnia
 Uptown
 .
 .
 chicago
(12 rows)

Using maplookup buffer_size

Use the buffer_size= parameter to indicate the maximum length of any value that maplookup returns for the virtual column you specify. If none of the returned key values can be greater than n bytes, use this parameter to allocate n bytes as the buffer_size.

For the next example, save this JSON data to a file, simple_name.json: 

{
  "name": "sierra",
  "age": "63",
  "eyes": "brown",
  "weapon": "doggie"
}
{
  "name": "janis",
  "age": "10",
  "eyes": "blue",
  "weapon": "humor"
}
{
  "name": "ben",
  "age": "43",
  "eyes": "blue",
  "weapon": "sword"
}
{
  "name": "jen",
  "age": "38",
  "eyes": "green",
  "weapon": "shopping"
}

1. Create a flex table, logs.
2. Load the simple_name.json data into logs, using the fjsonparser. Specify the flatten_arrays option as True: 

=> COPY logs FROM '/home/dbadmin/data/simple_name.json' 
  PARSER fjsonparser(flatten_arrays=True);

3. Use maplookup with buffer_size=0 for the logs table name key. This query returns all of the values:

=> SELECT MAPLOOKUP(__raw__, 'name' USING PARAMETERS buffer_size=0) FROM logs;
 MapLookup
-----------
 sierra
 ben
 janis
 jen
(4 rows)

4. Next, call maplookup() three times, specifying the buffer_size parameter as 3, 5, and 6, respectively. Now, maplookup() returns values with a byte length less than or equal to (<=) buffer_size: 

=> SELECT MAPLOOKUP(__raw__, 'name' USING PARAMETERS buffer_size=3) FROM logs;
 MapLookup
-----------

 ben

 jen
(4 rows)
=> SELECT MAPLOOKUP(__raw__, 'name' USING PARAMETERS buffer_size=5) FROM logs;
 MapLookup
-----------

 janis
 jen
 ben
(4 rows)

=> SELECT MAPLOOKUP(__raw__, 'name' USING PARAMETERS buffer_size=6) FROM logs;
 MapLookup
-----------
 sierra
 janis
 jen
 ben
(4 rows)

Disambiguate Empty Output Rows

This example shows how to interpret empty rows. Using maplookup without first checking whether a key exists can be ambiguous. When you review the following output, 12 empty rows, you cannot determine whether a user.location key has:

• A non-NULL value
• A NULL value
• No value

=> SELECT MAPLOOKUP(__raw__, 'user.location') FROM darkdata;
 maplookup
-----------












(12 rows)

To disambiguate empty output rows, use the mapcontainskey() function in conjunction with maplookup(). When maplookup returns an empty field, the corresponding value from mapcontainskey indicates t for a NULL or other value, or ffor no value.

The following example output using both functions lists rows with NULL or a name value as t, and rows with no value as f:  

=> SELECT MAPLOOKUP(__raw__, 'user.location'), MAPCONTAINSKEY(__raw__, 'user.location') 
FROM darkdata ORDER BY 1;
 maplookup | mapcontainskey
-----------+----------------
           | t
           | t
           | t
           | t
 Chile     | t
 Nesnia    | t
 Uptown    | t
 chicago   | t
           | f >>>>>>>>>>No value 
           | f >>>>>>>>>>No value
           | f >>>>>>>>>>No value 
           | f >>>>>>>>>>No value
(12 rows)

Check for Case-Sensitive Virtual Columns

You can use maplookup() with the case_sensitive parameter to return results when key names with different cases exist.

1. Save the following sample content as a JSON file. This example saves the file as repeated_key_name.json: 

{
  "test": "lower1"
}
{
  "TEST": "upper1"
}
{
  "TEst": "half1"
}
{
  "test": "lower2",
  "TEst": "half2"
}
{
  "TEST": "upper2",
  "TEst": "half3"  
}
{
  "test": "lower3",
  "TEST": "upper3"
} 
{
  "TEst": "half4",
  "test": "lower4",
  "TEST": "upper4"
} 
{
  "TesttestTesttestTesttestTesttestTesttestTesttestTesttestTesttestTesttestTesttestTesttestTesttest
TesttestTesttestTesttestTesttest":"1",
    "TesttestTesttestTesttestTesttestTesttestTesttestTesttestTesttestTesttestTesttestTesttest
TesttestTesttestTesttestTesttestTest12345":"2"
}

2. Create a flex table, dupe, and load the JSON file: 

=> CREATE FLEX TABLE dupe();
CREATE TABLE
dbt=> COPY dupe FROM '/home/release/KData/repeated_key_name.json' parser fjsonparser();
 Rows Loaded
-------------
           8
(1 row)