Flatten arrays in column having Json and also select other column values

esangrame

HI,

I have a table which has data as below

val1 | val2 | valjson
------+------+------------------------------------------------------------
x | y | {"str1": {"a": "11111","a_desc": "11111","a_loc": "1234"}}

Using below query I could parse the json field
SELECT MAPITEMS(MAPJSONEXTRACTOR(valjson USING PARAMETERS flatten_arrays = TRUE)) OVER(PARTITION BEST) FROM test_val ;

keys     | values 

-------------+--------
str1.a | 11111
str1.a_desc | 11111
str1.a_loc | 1234

My requirement is to achieve something as below
val1 | val2 | keys | values
------+------+------------+--------
x | y |str1.a | 11111
x | y |str1.a_desc | 11111
x | y |str1.a_loc | 1234

But if I use
SELECT val1,val2, MAPITEMS(MAPJSONEXTRACTOR(valjson USING PARAMETERS flatten_arrays = TRUE)) OVER(PARTITION BEST) FROM test_val ;
It gives error
[Vertica]VJDBC ERROR: Cannot specify anything other than user defined transforms and partitioning expressions in the SELECT list [SQL State=42601, DB Errorcode=2521]
1 statement failed.

Kindly help me in solving this.

DaveT

Instead of PARTITION BEST in the OVER clause use PARTITION BY VAL1, VAL2

esangrame

Thanks a lot.