22.3. Retrieving a Value from MAP Data as a List (map_extract) #
This stored procedure must be executed inside a duckdb.query() call as Postgres Pro AXE does not support the MAP data type.
Required privileges: Postgres Pro AXE administrator only. For a full list of stored procedures and privileges, refer to Section 12.1.
Execute the following command on the Postgres Pro AXE server:
SELECT * FROM duckdb.query('SELECT map_extract(MAP, key)') ON CONFLICT DO NOTHING;
Where:
MAP: The MAP data from which a value must be retrieved.You can specify a column with the MAP data type or a MAP literal.
key: The key for which a value must be retrieved.
Postgres Pro AXE returns a list containing the value for the specified key. If the data type of the specified key does not match the data type of the MAP key, an error is returned. If the MAP data does not contain the key, an empty list is returned.
Example 22.3. Executing the map_extract Stored Procedure
MAP data in the data column of the map_table table:
id | data
----+-------------------------
1 | {'a': 42, 'b': 43}
Retrieving a value for an existing key:
SELECT * FROM duckdb.query('SELECT map_extract(data, ''a'') FROM map_table') ON CONFLICT DO NOTHING;
[42]
Retrieving a value for a missing key:
SELECT * FROM duckdb.query('SELECT map_extract(data, ''c'') FROM map_table') ON CONFLICT DO NOTHING;
[]