109 lines
3.7 KiB
Plaintext
109 lines
3.7 KiB
Plaintext
### MAPs
|
|
|
|
# Since the keys of a JSON object must be strings, we'll use as an
|
|
# example of a MAP one which has integer keys:
|
|
|
|
D create or replace macro amap() as (SELECT MAP( [1,2,3], ['hello', 'world', '!']));
|
|
D select amap() "A Map";
|
|
┌─────────────────────────┐
|
|
│ A Map │
|
|
│ map(integer, varchar) │
|
|
├─────────────────────────┤
|
|
│ {1=hello, 2=world, 3=!} │
|
|
└─────────────────────────┘
|
|
|
|
# Iterating over the keys:
|
|
|
|
D select unnest(map_keys(amap())) as key;
|
|
┌───────┐
|
|
│ key │
|
|
│ int32 │
|
|
├───────┤
|
|
│ 1 │
|
|
│ 2 │
|
|
│ 3 │
|
|
└───────┘
|
|
|
|
# Iterating over the values:
|
|
D select unnest(map_values(amap())) as value;
|
|
┌─────────┐
|
|
│ value │
|
|
│ varchar │
|
|
├─────────┤
|
|
│ hello │
|
|
│ world │
|
|
│ ! │
|
|
└─────────┘
|
|
|
|
# One way to generate the key-value pairs is as follows:
|
|
D select unnest(map_keys(amap())) as key, unnest(map_values(amap())) as value;
|
|
┌───────┬─────────┐
|
|
│ key │ value │
|
|
│ int32 │ varchar │
|
|
├───────┼─────────┤
|
|
│ 1 │ hello │
|
|
│ 2 │ world │
|
|
│ 3 │ ! │
|
|
└───────┴─────────┘
|
|
|
|
# ... or avoiding calling amap() twice:
|
|
|
|
D select unnest(map_keys(m)) as key, unnest(map_values(m)) as value
|
|
from (select amap() m);
|
|
┌───────┬─────────┐
|
|
│ key │ value │
|
|
│ int32 │ varchar │
|
|
├───────┼─────────┤
|
|
│ 1 │ hello │
|
|
│ 2 │ world │
|
|
│ 3 │ ! │
|
|
└───────┴─────────┘
|
|
|
|
### JSON objects
|
|
|
|
# For simplicity, we'll the JSON entity `amap()::JSON` as an example object:
|
|
|
|
D select amap()::JSON as j;
|
|
┌───────────────────────────────────┐
|
|
│ j │
|
|
│ json │
|
|
├───────────────────────────────────┤
|
|
│ {"1":"hello","2":"world","3":"!"} │
|
|
└───────────────────────────────────┘
|
|
|
|
# To find the keys of a JSON object as a list,
|
|
# whence one can use iteration over lists:
|
|
|
|
D select json_keys(amap()::JSON) as keys;
|
|
┌───────────┐
|
|
│ keys │
|
|
│ varchar[] │
|
|
├───────────┤
|
|
│ [1, 2, 3] │
|
|
└───────────┘
|
|
|
|
# Similarly, to find the values in a JSON object as a list:
|
|
|
|
D select list_transform( json_keys(j), k -> (j->>k) ) as list
|
|
from (select amap()::JSON as j);
|
|
┌───────────────────┐
|
|
│ list │
|
|
│ varchar[] │
|
|
├───────────────────┤
|
|
│ [hello, world, !] │
|
|
└───────────────────┘
|
|
|
|
# Show the key-value pairs as a table:
|
|
D select unnest(json_keys(j)) as key,
|
|
unnest(list_transform(json_keys(j), k -> (j->>k))) as value
|
|
from (select amap()::JSON as j);
|
|
|
|
┌─────────┬─────────┐
|
|
│ key │ value │
|
|
│ varchar │ varchar │
|
|
├─────────┼─────────┤
|
|
│ 1 │ hello │
|
|
│ 2 │ world │
|
|
│ 3 │ ! │
|
|
└─────────┴─────────┘
|