query flex table for Json document

Ganesh_Arunasal · December 2013

Hi,
I have created flex table called cust in vertica by using the following 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" ]
]
}

But i am not sure how to query the data from database.

Can you pls help me to provide the select query for the same

Thanks,
Ganesh

[Deleted User] · December 2013

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)

Ganesh_Arunasal · January 2014

Hi James,

Thanks a lot. This is very helpful.

Regards
Ganesh

Adrian_1 · February 2014

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?

[Deleted User] · February 2014

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.

Adrian_1 · February 2014

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.

Daniel_Leybovic · February 2014

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)

Adrian_1 · February 2014

$ cat /tmp/flex.data  This file doesn't exist. Where can I find it?

Daniel_Leybovic · February 2014

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

query flex table for Json document

Comments

Leave a Comment