amazon redshift copy command json format

Question

I'm trying to copy into Redshift some json files with the following structure:

{
"data": [{
    "attr1": "value1",
    "attr2": "value2",
    "attr3": "value3",
},
{
    "attr1": "value4",
    "attr2": "value5",
    "attr3": "value6",
}]
}

The number of array elements is variable.

I've tried using the following jsonpath, but it doesn't work:

{
"jsonpaths": [
    "$.data[*].attr1",
    "$.data[*].attr2",
    "$.data[*].attr3"
]
}

If I use the following jsonpath, it only loads the first object in the array:

{
"jsonpaths": [
    "$.data[0].attr1",
    "$.data[0].attr2",
    "$.data[0].attr3"
]
}

Is there a way to do this?

Thanks!

systemjack systemjack · Accepted Answer · 2017-03-31T17:15:08

Your data structure is not quite right. For jsonpaths loading Redshift does not actually want the entire file to be one json structure. Each record is its own structure. So no commas separate records.

The top level elements for each record can either be all objects{} or arrays [], but the records are complete json structures and are separated by newlines and not commas. Check out this page for examples.

For your example like this:

{"data": {
    "attr1": "value1",
    "attr2": "value2",
    "attr3": "value3"
}}
{"data": {
    "attr1": "value4",
    "attr2": "value5",
    "attr3": "value6"
}}

{"jsonpaths": ["$.data.attr1", "$.data.attr2", "$.data.attr3"]}

or just:

{
    "attr1": "value1",
    "attr2": "value2",
    "attr3": "value3"
}
{
    "attr1": "value4",
    "attr2": "value5",
    "attr3": "value6"
}

{"jsonpaths": ["$.attr1", "$.attr2", "$.attr3"]}

amazon redshift copy command json format

1 Answers