Querying nested maps in flex tables

Given a JSON input file of

        "text": "text field 1",
        "tags": [
            "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;
                "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;

(1 row)

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


  • Options
    Prasanta_PalPrasanta_Pal - Select Field - Employee
    What is the output from the below query?

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

  • Options
    I figured it out. The correct query is: select maplookup(maplookup(__raw__, 'tags'),'1') from rma_test.flexdata2;
  • Options
    Prasanta_PalPrasanta_Pal - Select Field - Employee
    Great!!, probably should be an example how to do a query using nested maplookup
  • Options
    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"}
  • Options

    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
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file