Analyzing JSON Already Loaded into Vertica Regular Tables

Bryan_H · July 2019

(Thanks to Tom Wall for corrections)

Vertica flex tables allow you to query data in JSON format. But what if you've imported JSON objects into VARCHAR already? Here's how you can extract the JSON into flex tables without exportiing and importing.

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/9.2.x/HTML/Content/Authoring/FlexTables/FlexTableHandbook.htm
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/Functions/VerticaFunctions/FlexTables/maptostring.htm
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/Functions/VerticaFunctions/FlexTables/COMPUTE_FLEXTABLE_KEYS_AND_BUILD_VIEW.htm