JSON Payload Parsing & Querying

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)

That worked perfectly! Thank you so much for this!

Best Regards,
RedOc

Awesome!

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

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)

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

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;

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

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.