query flex table for Json document

When unsure of how to query Flexible Table data, the best first step is usually to call MapToString() to inspect the data's structure (it's whitespace formatting is lost below.)

Regarding your specific example, to avoid key-space explosion, the Flexible Tables JSON parser interprets arrays as sub-maps by default, and so you have to use explicit MapLookup() calls to retrieve data within nested arrays:


myuser=> CREATE FLEX TABLE MyTable();
CREATE TABLE
myuser=> COPY MyTable FROM STDIN PARSER FJsonParser();
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> { "cols": [ "Name", "address", "phone_no", "currency" ],
"data": [
[ "Edward", "477-7796 Faucibus. Street", "1-213-339-4865", "$84.36" ],
[ "Adrian", "P.O. Box 852, 4296 Interdum Avenue", "1-386-509-9907", "$43.67" ]
]
}>> >> >> >> >>
>> \.
myuser=> SELECT MapToString(__raw__) FROM MyTable;
MapToString
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
{
"cols":
{
"0": "Name",
"1": "address",
"2": "phone_no",
"3": "currency"
},
"data":
{
"0":
{
"0": "Edward",
"1": "477-7796 Faucibus. Street",
"2": "1-213-339-4865",
"3": "$84.36"
},
"1":
{
"0": "Adrian",
"1": "P.O. Box 852, 4296 Interdum Avenue",
"2": "1-386-509-9907",
"3": "$43.67"
}
}
}
(1 row)

myuser=> SELECT MapLookup(cols, '0'), MapLookup(cols, '1'), MapLookup(cols, '2'), MapLookup(cols, '3'), MapLookup(MapLookup(data, '0'), '0'), MapLookup(MapLookup(data, '0'), '1'), MapLookup(MapLookup(data, '0'), '2'), MapLookup(MapLookup(data, '0'), '3'), MapLookup(MapLookup(data, '1'), '0'), MapLookup(MapLookup(data, '1'), '1'), MapLookup(MapLookup(data, '1'), '2'), MapLookup(MapLookup(data, '1'), '3') FROM MyTable;
MapLookup | MapLookup | MapLookup | MapLookup | MapLookup | MapLookup | MapLookup | MapLookup | MapLookup | MapLookup | MapLookup | MapLookup
-----------+-----------+-----------+-----------+-----------+---------------------------+----------------+-----------+-----------+------------------------------------+----------------+-----------
Name | address | phone_no | currency | Edward | 477-7796 Faucibus. Street | 1-213-339-4865 | $84.36 | Adrian | P.O. Box 852, 4296 Interdum Avenue | 1-386-509-9907 | $43.67
(1 row)



To query directly without calls to MapLookup(), you can load your data with array flattening turned on, then query the columns directly:


myuser=> COPY MyTable FROM STDIN PARSER FJsonParser(flatten_arrays=True);
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> { "cols": [ "Name", "address", "phone_no", "currency" ],
"data": [
[ "Edward", "477-7796 Faucibus. Street", "1-213-339-4865", "$84.36" ],
[ "Adrian", "P.O. Box 852, 4296 Interdum Avenue", "1-386-509-9907", "$43.67" ]
]
}>> >> >> >> >>
>> \.
myuser=> SELECT MapToString(__raw__) FROM MyTable;
MapToString
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
{
"cols.0": "Name",
"cols.1": "address",
"cols.2": "phone_no",
"cols.3": "currency",
"data.0.0": "Edward",
"data.0.1": "477-7796 Faucibus. Street",
"data.0.2": "1-213-339-4865",
"data.0.3": "$84.36",
"data.1.0": "Adrian",
"data.1.1": "P.O. Box 852, 4296 Interdum Avenue",
"data.1.2": "1-386-509-9907",
"data.1.3": "$43.67"
}
(1 row)

myuser=> SELECT "cols.0", "cols.1", "cols.2", "cols.3", "data.0.0", "data.0.1", "data.0.2", "data.0.3", "data.1.0", "data.1.1", "data.1.2", "data.1.3" FROM MyTable;
cols.0 | cols.1 | cols.2 | cols.3 | data.0.0 | data.0.1 | data.0.2 | data.0.3 | data.1.0 | data.1.1 | data.1.2 | data.1.3
--------+---------+----------+----------+----------+---------------------------+----------------+----------+----------+------------------------------------+----------------+----------
Name | address | phone_no | currency | Edward | 477-7796 Faucibus. Street | 1-213-339-4865 | $84.36 | Adrian | P.O. Box 852, 4296 Interdum Avenue | 1-386-509-9907 | $43.67
(1 row)

Hi James,

Thanks a lot. This is very helpful.

Regards
Ganesh

Hi Ganesh, hi James,

How do I go further now? I'd like to have the result: Column titles as shown above and the two sets directly "below" these column headings:

|  Name |  Address     |  Phone | Currency | |Edward | 477.. street | 1-2... | $84.36   | | Adrian| P.O. Box ..  | 1-3... | $43.67   | 

Is it possible to realize it in an easy way? 

Absolutely. For this file, use the FDelimitedParser. For a concrete example:

jfraumeni=> CREATE FLEX TABLE foo();
CREATE TABLE
jfraumeni=> COPY foo FROM STDIN PARSER FDelimitedParser() DIRECT;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> |  Name |  Address     |  Phone | Currency |
|Edward | 477.. street | 1-2... | $84.36   |
| Adrian| P.O. Box ..  | 1-3... | $43.67   |
>> \.
jfraumeni=> SELECT MapToString(__raw__) FROM foo;
                                                      MapToString
-----------------------------------------------------------------------------------------------------------------------
 {
   "" : "",
   "Address" : "477.. street",
   "Currency" : "$84.36",
   "Name" : "Edward",
   "Phone" : "1-2..."
}

 {
   "" : "",
   "Address" : "P.O. Box ..",
   "Currency" : "$43.67",
   "Name" : "Adrian",
   "Phone" : "1-3..."
}
(2 rows)

Note that by having leading and trailing |'s. you're adding a column with an empty name and empty value in the spirit of Flex accepting whatever data it can.

Thank you for your reply. However, I wanted to know something else:

Your reply to Ganesh's question included the following code:

myuser=> SELECT MapToString(__raw__) FROM MyTable;
MapToString
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------{
"cols":
{
"0": "Name",
"1": "address",
"2": "phone_no",
"3": "currency"
},
"data":
{
"0":
{
"0": "Edward",
"1": "477-7796 Faucibus. Street",
"2": "1-213-339-4865",
"3": "$84.36"
},
"1":
{
"0": "Adrian",
"1": "P.O. Box 852, 4296 Interdum Avenue",
"2": "1-386-509-9907",
"3": "$43.67"
}
}
}

Now, my question is whether it is possible to automatically transform the table having the attributes name, address, phone_no and currency with the two sets below. Assuming that this is possible, the result of the query "SELECT name, address, phone_no, currency FROM my table;" should look as follows:

Name  |  Address    |  Phone | Currency

- - - - - + - - - - - - - - + - - - - - + - - - - - -

edward | 477.. street | 1-2...   | $84.36  

Adrian | P.O. Box ..   | 1-3...   | $43.67  

I'm asking because I'm currently testing to load semi- and unstructured data from different sources into Vertica.

Thank you for your reply.

May be this can help:

$ cat /tmp/flex.data 
{ "cols": [ "Name", "address", "phone_no", "currency" ],
"data": [
              [ "Edward", "477-7796 Faucibus. Street", "1-213-339-4865", "$84.36" ],
              [ "Adrian", "P.O. Box 852, 4296 Interdum Avenue", "1-386-509-9907", "$43.67" ]
            ]
}

$ /tmp/parse_json.py /tmp/flex.data 
Name|address|phone_no|currency
Edward|477-7796 Faucibus. Street|1-213-339-4865|$84.36
Adrian|P.O. Box 852, 4296 Interdum Avenue|1-386-509-9907|$43.67

Code snippet:

#!/usr/bin/env python
# -*- coding: utf-8 -*-
import sys
import json

json_file = sys.argv[1]

data = json.load(open(json_file, 'r'))
print '|'.join(data['cols'])
for record in data['data']:
    print '|'.join(record)

>> This file doesn't exist. Where can I find it?
O_o. Forget about my comment.