Vertica Analytics Platform Version 9.2.x Documentation

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);
  4. Use maplookup with buffer_size=0 for the logs table name key. This query returns all of the values:
  5. => SELECT MAPLOOKUP(__raw__, 'name' USING PARAMETERS buffer_size=0) FROM logs;
     MapLookup
    -----------
     sierra
     ben
     janis
     jen
    (4 rows)
    
  6. 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
  7. => 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
  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"
    }
    
  3. Create a flex table, dupe, and load the JSON file: 
  4. => CREATE FLEX TABLE dupe();
    CREATE TABLE
    dbt=> COPY dupe FROM '/home/release/KData/repeated_key_name.json' parser fjsonparser();
     Rows Loaded
    -------------
               8
    (1 row)