
Example:
Let’s create a sample table with a JSON column to convert:
CREATE TABLE fruits (id INTEGER, description VARCHAR(255));
INSERT INTO fruits VALUES (1,'{"id": 1, "x": "Apples", "value": "128.14", "fill": "green"}');
INSERT INTO fruits VALUES (2,'{"id": 2, "x": "Oranges", "value": "64.07", "fill": "orange"}');
Now let’s create a flex table:
CREATE TABLE fruits_flex();
Flex tables use a custom encoding that differs from JSON. We need to convert the ‘description’ column into the flex VMap format with MapJSONExtractor to a column named ‘__raw__’:
INSERT INTO fruits_flex SELECT MAPJsonExtractor(description) AS __raw__ FROM fruits;
Let’s look at our flex data:
SELECT MAPTOSTRING(__raw__) FROM fruits_flex;
maptostring
-----------------------------------------------------------------------------------
{
"fill": "green",
"id": "1",
"value": "128.14",
"x": "Apples"
}
{
"fill": "orange",
"id": "2",
"value": "64.07",
"x": "Oranges"
}
(2 rows)
And build a SQL view onto the flex table:
dbadmin=> SELECT compute_flextable_keys_and_build_view('fruits_flex');
compute_flextable_keys_and_build_view
------------------------------------------------------------------------------------------------------------
Please see public.fruits_flex_keys for updated keys
The view public.fruits_flex_view is ready for querying
(1 row)
dbadmin=> select * from public.fruits_flex_view;
fill | id | value | x
--------+----+---------+---------
green | 1 | 128.140 | Apples
orange | 2 | 64.070 | Oranges
(2 rows)
This might come in handy, for example, if you’re collecting streaming JSON object data for later analysis. Enjoy!Helpful Links:
https://www.vertica.com/docs/latest/HTML/Content/Authoring/FlexTables/FlexTableHandbook.htm https://www.vertica.com/docs/latest/HTML/Content/Authoring/SQLReferenceManual/Functions/VerticaFunctions/FlexTables/maptostring.htm https://www.vertica.com/docs/latest/HTML/Content/Authoring/SQLReferenceManual/Functions/VerticaFunctions/FlexTables/COMPUTE_FLEXTABLE_KEYS_AND_BUILD_VIEW.htm
Have fun!