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

VMap_data

Any VMap data. The VMap can exist as:

  • The __raw__ column of a flex table
  • Data returned from a map function such as maplookup()
  • Other database content 
virtual_column_name
 The name of the virtual column whose values this function returns. 
buffer_size

[Optional parameter] Specifies the maximum length (in bytes) of each value returned for virtual_column_name. To return all values for virtual_column_name, specify a buffer_size equal to or greater than (=>) the number of bytes for any returned value. Any returned values greater in length than buffer_size are rejected.

Default value: 0 (No limit on buffer_size)

 
case_sensitive

[Optional parameter]

Specifies whether to return values for virtual_column_name if keys with different cases exist.

Example:

(... USING PARAMETERS case_sensitive=true)

Default value: false

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: 
    3. => 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: 
    4. => 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:  
    5. => 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:

=> 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: 
    2. {
  "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:  
    3. => CREATE FLEX TABLE dupe();
CREATE TABLE
dbt=> COPY dupe FROM '/home/release/KData/repeated_key_name.json' parser fjsonparser();
 Rows Loaded
-------------
           8
(1 row)

 

See Also