Querying nested maps in flex tables

The_Mad_Zymurgi

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.

Prasanta_Pal

What is the output from the below query?

=> select * from rma_test.flexdata2_keys;

The_Mad_Zymurgi

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

The_Mad_Zymurgi

I figured it out. The correct query is: select maplookup(maplookup(__raw__, 'tags'),'1') from rma_test.flexdata2;

Prasanta_Pal

Great!!, probably should be an example how to do a query using nested maplookup

bizob2828

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"}

RM

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!