类别：: 半结构化和结构化数据函数（映射）

MAP_INSERT¶

Returns a new MAP consisting of the input MAP with a new key-value pair inserted. That is, an existing key is updated with a new value.

语法¶

MAP_INSERT( <map> , <key> , <value> [ , <updateFlag> ] )

Copy

实参¶

map: 向其中插入新键值对的源映射。
key: 要插入到映射中的新键。必须与映射中的所有现有键不同，除非 updateFlag 设置为 TRUE。
value: 与键关联的值。

可选

updateFlag

A Boolean flag that, when set to TRUE, specifies the input value is used to update an existing value for a key in the map, rather than inserting a new key-value pair.

默认为 FALSE。

返回¶

返回由输入 MAP 组成的 MAP，其中插入了新的键值对或用新值更新了现有键。

使用说明¶

键表达式的类型必须与映射的键的类型匹配。如果类型为 VARCHAR，则类型可以是不同的长度。
The function supports JSON null values, but not SQL NULL values or keys:
- If key is any string other than NULL and value is a JSON null (for example, PARSE_JSON('NULL')), then the key-value pair is inserted into the returned map.
- If key is any string other than NULL and value is a SQL NULL (for example, NULL), then the value is converted to JSON null, and the key-value pair is inserted into the returned map.
- 如果 key 是 SQL NULL，则从返回的映射中省略键值对。
如果 updateFlag 设置为 TRUE，则现有输入 key 将更新为输入 value。如果 updateFlag 已省略或设置为 FALSE，并且输入键已存在于映射中，则返回错误。
如果 updateFlag 设置为 TRUE，但映射中尚不存在相应的键，则添加键值对。

示例¶

将第三个键值对插入到包含两个键值对的映射中：

SELECT MAP_INSERT({'a':1,'b':2}::MAP(VARCHAR,NUMBER),'c',3);

Copy

+------------------------------------------------------+
| MAP_INSERT({'A':1,'B':2}::MAP(VARCHAR,NUMBER),'C',3) |
|------------------------------------------------------|
| {                                                    |
|   "a": 1,                                            |
|   "b": 2,                                            |
|   "c": 3                                             |
| }                                                    |
+------------------------------------------------------+

将两个新的键值对插入到空映射中，同时省略一个键值对：

Key_One consists of a JSON null value.
Key_Two consists of a SQL NULL value, which is converted to a JSON null value.
Key_Three 由包含“null”的字符串组成。

SELECT MAP_INSERT(MAP_INSERT(MAP_INSERT({}::MAP(VARCHAR,VARCHAR),
  'Key_One', PARSE_JSON('NULL')), 'Key_Two', NULL), 'Key_Three', 'null');

Copy

+---------------------------------------------------------------------------+
| MAP_INSERT(MAP_INSERT(MAP_INSERT({}::MAP(VARCHAR,VARCHAR),                |
|    'KEY_ONE', PARSE_JSON('NULL')), 'KEY_TWO', NULL), 'KEY_THREE', 'NULL') |
|---------------------------------------------------------------------------|
| {                                                                         |
|   "Key_One": null,                                                        |
|   "Key_Three": "null",                                                    |
|   "Key_Two": null                                                         |
| }                                                                         |
+---------------------------------------------------------------------------+

使用新值 ("string-value") 更新现有键值对 ("k1": 100)：

SELECT MAP_INSERT({'k1':100}::MAP(VARCHAR,VARCHAR), 'k1', 'string-value', TRUE) AS map;

Copy

+------------------------+
| MAP                    |
|------------------------|
| {                      |
|   "k1": "string-value" |
| }                      |
+------------------------+

Create a temporary table that contains MAP values:

CREATE OR REPLACE TEMP TABLE demo_maps(
  id INTEGER,
  attrs MAP(VARCHAR, VARCHAR),
  defaults MAP(VARCHAR, VARCHAR),
  keep_keys ARRAY(VARCHAR),
  ins_key VARCHAR,
  ins_val VARCHAR,
  update_existing BOOLEAN,
  del_key1 VARCHAR,
  del_key2 VARCHAR);

INSERT INTO demo_maps SELECT
  1,
  {'color':'red','size':'M','brand':'Acme'}::MAP(VARCHAR, VARCHAR),
  {'currency':'USD','size':'L'}::MAP(VARCHAR, VARCHAR),
  ['color','brand']::ARRAY(VARCHAR),
  'material',
  'cotton',
  TRUE,
  'size',
  'brand';

INSERT INTO demo_maps SELECT
  2,
  {'color':'blue','brand':'ZenCo'}::MAP(VARCHAR, VARCHAR),
  {'currency':'EUR','size':'M','brand':'ZenCo'}::MAP(VARCHAR, VARCHAR),
  ['brand','currency']::ARRAY(VARCHAR),
  'brand',
  'ZC',
  FALSE,
  'currency',
  'material';

Copy

查询表以显示数据：

SELECT * FROM demo_maps;

Copy

+----+---------------------+----------------------+--------------+----------+---------+-----------------+----------+----------+
| ID | ATTRS               | DEFAULTS             | KEEP_KEYS    | INS_KEY  | INS_VAL | UPDATE_EXISTING | DEL_KEY1 | DEL_KEY2 |
|----+---------------------+----------------------+--------------+----------+---------+-----------------+----------+----------|
|  1 | {                   | {                    | [            | material | cotton  | True            | size     | brand    |
|    |   "brand": "Acme",  |   "currency": "USD", |   "color",   |          |         |                 |          |          |
|    |   "color": "red",   |   "size": "L"        |   "brand"    |          |         |                 |          |          |
|    |   "size": "M"       | }                    | ]            |          |         |                 |          |          |
|    | }                   |                      |              |          |         |                 |          |          |
|  2 | {                   | {                    | [            | brand    | ZC      | False           | currency | material |
|    |   "brand": "ZenCo", |   "brand": "ZenCo",  |   "brand",   |          |         |                 |          |          |
|    |   "color": "blue"   |   "currency": "EUR", |   "currency" |          |         |                 |          |          |
|    | }                   |   "size": "M"        | ]            |          |         |                 |          |          |
|    |                     | }                    |              |          |         |                 |          |          |
+----+---------------------+----------------------+--------------+----------+---------+-----------------+----------+----------+

Using the keys in the ins_key column and the values in the ins_val column, insert or update key-value pairs in the maps in the attrs column:

SELECT id, MAP_INSERT(attrs, ins_key, ins_val, TRUE) AS attrs_insert_or_update
  FROM demo_maps;

Copy

+----+-------------------------+
| ID | ATTRS_INSERT_OR_UPDATE  |
|----+-------------------------|
|  1 | {                       |
|    |   "brand": "Acme",      |
|    |   "color": "red",       |
|    |   "material": "cotton", |
|    |   "size": "M"           |
|    | }                       |
|  2 | {                       |
|    |   "brand": "ZC",        |
|    |   "color": "blue"       |
|    | }                       |
+----+-------------------------+