Hey I noticed that the MAPTOSTRING built-in produces JSON that is not quite what one loaded in, even when using FJSONParser(flatten_maps=FALSE,flatten_arrays=FALSE). I leafed through some of the available source https://github.com/vertica/FlexTable/blob/master/src/JSONParser.cpp I'm guessing that's by design as all flex inputs are turned into the VMap internal representation which doesn't have a notion of "arrays" or "value types"
Here's a minimal example:
CREATE FLEX TABLE some_json();
my json:
{
"stuff": [
"a",
"b",
"c",
"d"
],
"id": 0
}
{
"stuff": [
"e",
"f",
"g",
"h"
],
"id": 1
}
loading it in:
$ echo '{"stuff": ["a", "b", "c", "d"], "id": 0}\n{"stuff": ["e", "f", "g", "h"], "id": 1}' | vsql ... -c "COPY some_json(__raw__) FROM STDIN PARSER public.FJSONParser(flatten_maps=FALSE,flatten_arrays=FALSE)"
what it looks like
$ vsql ... -c "SELECT public.MapToString(__raw__) FROM some_json"
MapToString
-------------------------------------------------------------------------------
{
"id": "0",
"stuff": {
"0": "a",
"1": "b",
"2": "c",
"3": "d"
}
}
{
"id": "1",
"stuff": {
"0": "e",
"1": "f",
"2": "g",
"3": "h"
}
}
(2 rows)
Is there a way of keeping around the originally loaded data alongside the VMAP column?
Thanks!
