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

flex table

Hi,
Is vertica flex table support the complex json document  (ie json document contains array type). If it Yes, could you please provide some examples of query the array data from vertica database.

Thanks,
Ganesh

Comments

  • I am waiting on the same issues, how to query arrays from flex tables.
  • Json array example ... look at "friends" columns:
    [    {        "id": 0,        "guid": "9388fc76-b570-4019-b54c-d306a18e40b3",        "isActive": true,        "balance": "$3,118.00",        "picture": "http://placehold.it/32x32";,        "age": 24,        "name": "Baxter Franks",        "gender": "male",        "company": "Eclipsent",        "email": "[email protected]",        "phone": "+1 (921) 456-3029",        "address": "294 Plaza Street, Grantville, South Carolina, 840",        "about": "Officia voluptate cillum magna et exercitation officia ex voluptate ullamco. Sint aute ea aute excepteur in et tempor qui minim nulla deserunt cillum. Anim irure amet aliqua sit. Irure officia velit irure occaecat elit reprehenderit pariatur.\r\n",        "registered": "1992-09-27T17:41:11 -03:00",        "latitude": -15.004801,        "longitude": 35.372527,        "tags": [            "non",            "sint",            "et",            "Lorem",            "irure",            "duis",            "fugiat"        ],        "friends": [            {                "id": 0,                "name": "Byers Olsen"            },            {                "id": 1,                "name": "Preston Nunez"            },            {                "id": 2,                "name": "Bernadine Sharp"            }        ],        "randomArrayItem": "apple"    },
  • is it true that the only way to do it is via "flatten_arrays=true" at json parser level?
  • Hi all,

    Apologies for the delay -- yesterday and today are holidays in many countries (including the US where we're based), so the forums are likely to be rather slow.

    Vertica treats JSON arrays the same way that JavaScript itself does -- namely, as a nested object/map whose keys are the array indices.  (The syntax for accessing an array in JavaScript is the same as the syntax for accessing a member of an object.)

    You do not need to use flatten_arrays.  However, flatten_arrays does affect how you get at these objects.

    With flatten_arrays, for example, the column "tags.0" (yes, a column name with a "." in it; quoting required) will contain "non".

    Without it, the column "tags" will contain a Map record equivalent to (in JSON) '{ "0": "non", "1": "sint", "2": "et", "3": "Lorem", "4": "irure", "5": "duis", "6": "fugiat" }'.  (Our internal representation is optimized / not actually raw JSON.)  Use the "maplookup(map, key)" scalar function to look up a key in a Map record.  See the documentation for more information -- Flex comes with a bunch of utility functions that you should be aware of.

    The "flatten_arrays" flag is intended to make the data be a little easier to query in some cases.  Fewer function calls, etc.  But it's less "correct" in some sense; you're getting rid of hierarchy that was present in the raw data.

    More feedback is, of course, always welcome.

    Adam

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.