The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.

Querying nested maps in flex tables

Given a JSON input file of

[
    {
        "text": "text field 1",
        "tags": [
            "Tag1",
            "2nd Tag",
            "Last Tag in Array"
        ]
    }
]

I've done the following

copy rma_test.flexdata2 from '/data/tmp/short.json' parser fjsonparser();  - note I did not flatten the array

select compute_flextable_keys_and_build_view('rma_test.flexdata2');

I can see that the data has been loaded correctly.

dbadmin=> select maptostring(__raw__) from rma_test.flexdata2;
                                                 maptostring                                                
-------------------------------------------------------------------------------------------------------------
 {
        "tags":
         {
                "0":    "Tag1",
                "1":    "2nd Tag",
                "2":    "Last Tag in Array"
         },
        "text": "text field 1"
 }
(1 row)

How can I create a query that returns just the 2nd tag field (which appears to have an array subscript of 1)?

This what I thought it would be, but it doesn't seem to work.  I get a blank row.

dbadmin=> select maplookup(__raw__, 'tags.1') from rma_test.flexdata2;

 maplookup
-----------
 
(1 row)

Obviously, I'm having a basic misunderstanding of how querying nested maps works.  Any clarification would be most appreciated.

Comments

  • What is the output from the below query?

    => select * from rma_test.flexdata2_keys;
  • dbadmin=> select * from rma_test.flexdata2_keys;
     key_name | frequency |   data_type_guess  
    ----------+-----------+---------------------
     tags     |         1 | long varbinary(142)
     text     |         1 | varchar(24)


  • I figured it out. The correct query is: select maplookup(maplookup(__raw__, 'tags'),'1') from rma_test.flexdata2;
  • Great!!, probably should be an example how to do a query using nested maplookup
  • on this same note how can i get all the tags in one query? and i mean not by doing select "tags.0", "tags.1", "tags.2".  I want to be able to get "tags".  Same in the case if it is a nested object
    { "id" : 1,
      "data": {
       "key": "value",
       "key1": "value1"
      } }
    How can i just select "data" and it returns the object of { "key": "value", "key1": "value1"}
  • I am also looking for the same question:

     

    "How can i just select "data" and it returns the object of { "key": "value", "key1": "value1"}"

     

    Did you get any solution?

     

    Thanks in advance!

Leave a Comment

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

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.