Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

query flex table for Json document

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

Comments

  •     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)

  • $ cat /tmp/flex.data  This file doesn't exist. Where can I find it?
  • >> This file doesn't exist. Where can I find it?
    O_o. Forget about my comment.

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.