JSON Payload Parsing & Querying

RedOc

I am using Vertica DB (and DBeaver as SQL Editor) - I am new to both tools.

I have a view with multiple columns:

a | xyza | cont | json
5 | 1542 | none | {"range":23, "rm": 51, "spx": 30}
5 | 1442 | none | {"range":24, "rm": 50, "spx": 3 }
3 | 1462 | none | {"range":24, "rm": 50, "spx": 30}

(int) | (int) | (Varchar) | (Long Varchar)

I want to create another view (or for the beginning, just be able to query it properly) of the above, but with the "json" column separated into the individual fields/columns "range", "rm" and "spx".

I imagine the output of the query / the new view to be something like the following:

a | xyza | cont | range | rm | spx
5 | 1542 | none | 23 | 51 | 30
5 | 1442 | none | 24 | 50 | 3
....

So far I have not been able to even query the "range" for example.

Hence my questions:

1. How can I separate the json column key-value structure into individual columns (in a query output)?

2. How can I transfer the desired output into a new view in Vertica?

Best regards
Victor

Jim_Knicely

Hi,

Maybe like this?

dbadmin=> SELECT * FROM test;
 a | xyza | cont |               json
---+------+------+-----------------------------------
 3 | 1462 | none | {"range":24, "rm": 50, "spx": 30}
 5 | 1442 | none | {"range":24, "rm": 50, "spx": 3 }
 5 | 1542 | none | {"range":23, "rm": 51, "spx": 30}
(3 rows)

dbadmin=> CREATE OR REPLACE VIEW test_vw AS
dbadmin-> SELECT a, xyza, cont,
dbadmin->        MAPLOOKUP(MapJSONExtractor(json), 'range') AS range,
dbadmin->        MAPLOOKUP(MapJSONExtractor(json), 'rm') AS rm,
dbadmin->        MAPLOOKUP(MapJSONExtractor(json), 'spx') AS spx
dbadmin->   FROM test;
CREATE VIEW

dbadmin=> SELECT * FROM test_vw;
 a | xyza | cont | range | rm | spx
---+------+------+-------+----+-----
 3 | 1462 | none | 24    | 50 | 30
 5 | 1442 | none | 24    | 50 | 3
 5 | 1542 | none | 23    | 51 | 30
(3 rows)

RedOc

That worked perfectly! Thank you so much for this!

Best Regards,
RedOc

Jim_Knicely

Awesome!

Ariel_Cary

Hi Victor. Have you met the bracket operator to query map data? It avoids manually calling maplookup, and quite useful to query nested data.

select (MapJSONExtractor('{"range":24, "rm": 50, "spx": 30}'))['range'];
 MapJSONExtractor
------------------
 24
(1 row)

-- Set flatten_maps=false to preserve nested structure
select (MapJSONExtractor('{"range":{"nested_range":24}, "rm": 50, "spx": 30}' using parameters flatten_maps=false))['range']['nested_range'];
 MapJSONExtractor
------------------
 24
(1 row)

https://www.vertica.com/docs/9.1.x/HTML/index.htm#Authoring/FlexTables/QueryNestedData.htm

Jim_Knicely

Nice!

dbadmin=> CREATE OR REPLACE VIEW test_vw2 AS
dbadmin->   SELECT a, xyza, cont,
dbadmin->          (MapJSONExtractor(json))['range'] AS range,
dbadmin->          (MapJSONExtractor(json))['rm'] AS rm,
dbadmin->          (MapJSONExtractor(json))['spx'] AS spx
dbadmin->     FROM test;
CREATE VIEW

dbadmin=> SELECT * FROM test_vw2;
 a | xyza | cont | range | rm | spx
---+------+------+-------+----+-----
 3 | 1462 | none | 24    | 50 | 30
 5 | 1442 | none | 24    | 50 | 3
 5 | 1542 | none | 23    | 51 | 30
(3 rows)

RedOc

Thank you both so much for your input! It really helped me to finish my task

martinc123

Is there a way how to use a 'bracket operator' directly on mapped values from json array? Using json data from vertica link showed above, I want to list all the items in the menu for restaurant:

This does not work:

with json as (select MapJSONExtractor(' { "restaurant" : { "_name_" : "Bob''s pizzeria", "cuisine" : "Italian", "location" : {"city" : "Cambridge", "zip" : "02140"}, "menu" : [{"item" : "cheese pizza", "price" : "$8.25"}, {"item" : "chicken pizza", "price" : "$11.99"}, {"item" : "spinach pizza", "price" : "$10.50"}] } }'using parameters flatten_maps=false) as vals) select json.vals['restaurant']['_name_'] as restaurant,MAPVALUES(json.vals['restaurant']['menu']) OVER (PARTITION by json.vals['restaurant']['_name_'])['item'] from json; ERROR: Syntax error at or near "[">

So i have to wrap the query inline and select from it:

`with json as
(select MapJSONExtractor('
{
"restaurant" : {
"name" : "Bob''s pizzeria",
"cuisine" : "Italian",
"location" : {"city" : "Cambridge", "zip" : "02140"},
"menu" : [{"item" : "cheese pizza", "price" : "$8.25"},
{"item" : "chicken pizza", "price" : "$11.99"},
{"item" : "spinach pizza", "price" : "$10.50"}]
}
}'using parameters flatten_maps=false) as vals)
select restaurant, menu['item'] from (
select json.vals['restaurant']['name'] as restaurant,MAPVALUES(json.vals['restaurant']['menu']) OVER (PARTITION by json.vals['restaurant']['name']) as menu
from json
) j;

restaurant	menu
Bob's pizzeria	cheese pizza
Bob's pizzeria	chicken pizza
Bob's pizzeria	spinach pizza `

I have a json with multiple nested arrays and there a lot of nested sql needed to extract them ..

martinc123

Is there a way how to use 'bracket operator', or maplookup on mapped values from an json array?

Using json data from vertica link above, I am trying to get all menu items for each restaurant:

with json as
(select MapJSONExtractor('
{
"restaurant" : {
"name" : "Bob''s pizzeria",
"cuisine" : "Italian",
"location" : {"city" : "Cambridge", "zip" : "02140"},
"menu" : [{"item" : "cheese pizza", "price" : "$8.25"},
{"item" : "chicken pizza", "price" : "$11.99"},
{"item" : "spinach pizza", "price" : "$10.50"}]
}
}'using parameters flatten_maps=false) as vals)
select json.vals['restaurant']['name'] as restaurant,MAPVALUES(json.vals['restaurant']['menu']) OVER (PARTITION by json.vals['restaurant']['name'])['item']
from json;

Syntax error at or near "["

Last "['item']" is not working.

I have to nest it and use inline subquery to get the results:

with json as
(select MapJSONExtractor('
{
"restaurant" : {
"name" : "Bob''s pizzeria",
"cuisine" : "Italian",
"location" : {"city" : "Cambridge", "zip" : "02140"},
"menu" : [{"item" : "cheese pizza", "price" : "$8.25"},
{"item" : "chicken pizza", "price" : "$11.99"},
{"item" : "spinach pizza", "price" : "$10.50"}]
}
}'using parameters flatten_maps=false) as vals)
select restaurant, menu['item'] from (
select json.vals['restaurant']['name'] as restaurant,MAPVALUES(json.vals['restaurant']['menu']) OVER (PARTITION by json.vals['restaurant']['name']) as menu
from json
) j;

restaurant	menu
Bob's pizzeria	cheese pizza
Bob's pizzeria	chicken pizza
Bob's pizzeria	spinach pizza

I have a json with multiple nested arrays and extracting data means a lot of nested subqueries needed.