JSON Payload Parsing & Querying

RedOcRedOc Registered User
edited September 26 in Vertica Forum

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

Comments

  • Jim_KnicelyJim_Knicely Administrator, Moderator, Employee, Registered User, VerticaExpert

    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)
    
  • RedOcRedOc Registered User

    That worked perfectly! Thank you so much for this!

    Best Regards,
    RedOc

  • Jim_KnicelyJim_Knicely Administrator, Moderator, Employee, Registered User, VerticaExpert

    Awesome!

  • Ariel_CaryAriel_Cary Employee, Registered User, VerticaExpert

    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_KnicelyJim_Knicely Administrator, Moderator, Employee, Registered User, VerticaExpert
    edited October 1

    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)
    
  • RedOcRedOc Registered User

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

  • martinc123martinc123 Registered User

    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.

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file