flex table

Ganesh_Arunasal

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

Gil_Peretz

I am waiting on the same issues, how to query arrays from flex tables.

Gil_Peretz

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": "baxterfranks@eclipsent.com",        "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"    },

Gil_Peretz

is it true that the only way to do it is via "flatten_arrays=true" at json parser level?

[Deleted User]

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