docs/en/sql-reference/sql-functions/dict-functions/dictionary_get.md
Query the value mapped to the key in a dictionary object.
dictionary_get('dictionary_object_name', key_expression_list, [NULL_IF_NOT_EXIST])
key_expression_list ::=
key_expression [, ...]
key_expression ::=
column_name | const_value
dictionary_name: The name of the dictionary object.key_expression_list: A list of expressions for all key columns. It can be a list of column names or a list of values.NULL_IF_NOT_EXIST (Optional): Whether to return if the key does not exist in the dictionary cache. Valid values:
true: Null is returned if the key does not exist.false (Default): An exception is thrown if the key does not exist.Returns the values of value columns as a STRUCT type. Therefore, you can use [N] or .<column_name> to specify a particular column's value. N represents the column's position, starting from 1.
The following examples uses the dataset from the examples of dict_mapping.
Example 1: Query the values of the value column mapped to the key column order_uuid in the dictionary object dict_obj.
MySQL > SELECT dictionary_get('dict_obj', order_uuid) FROM dict;
+--------------------+
| DICTIONARY_GET |
+--------------------+
| {"order_id_int":1} |
| {"order_id_int":3} |
| {"order_id_int":2} |
+--------------------+
3 rows in set (0.02 sec)
Example 2: Query the value of the value column mapped to key a1 in the dictionary object dict_obj.
MySQL > SELECT dictionary_get("dict_obj", "a1");
+--------------------+
| DICTIONARY_GET |
+--------------------+
| {"order_id_int":1} |
+--------------------+
1 row in set (0.01 sec)
Example 3: Query the values of the value columns mapped to key 1 in the dictionary object dimension_obj.
MySQL > SELECT dictionary_get("dimension_obj", 1);
+-----------------------------------------------------------------------------------------------------------------+
| DICTIONARY_GET |
+-----------------------------------------------------------------------------------------------------------------+
| {"ProductName":"T-Shirt","Category":"Apparel","SubCategory":"Shirts","Brand":"BrandA","Color":"Red","Size":"M"} |
+-----------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
Example 4: Query the value of the first value column mapped to key 1 in the dictionary object dimension_obj.
MySQL > SELECT dictionary_get("dimension_obj", 1)[1];
+-------------------+
| DICTIONARY_GET[1] |
+-------------------+
| T-Shirt |
+-------------------+
1 row in set (0.01 sec)
Example 5: Query the value of the second value column mapped to key 1 in the dictionary object dimension_obj.
MySQL > SELECT dictionary_get("dimension_obj", 1)[2];
+-------------------+
| DICTIONARY_GET[2] |
+-------------------+
| Apparel |
+-------------------+
1 row in set (0.01 sec)
Example 6: Query the value of ProductName value column mapped to key 1 in the dictionary object dimension_obj.
MySQL > SELECT dictionary_get("dimension_obj", 1).ProductName;
+----------------------------+
| DICTIONARY_GET.ProductName |
+----------------------------+
| T-Shirt |
+----------------------------+
1 row in set (0.01 sec)