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 · Accepted Answer · 2017-03-31 18:00:36Z

4

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"]}

edited Mar 31, 2017 at 18:00

answered Mar 31, 2017 at 17:15

systemjack

3,0051 gold badge21 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mihai Tache Over a year ago

Thanks for the answer! I saw the examples page, but I thought there might be a way to create a jsonpath for my case. Unfortunately, that's how I receive the data.

Collectives™ on Stack Overflow

amazon redshift copy command json format

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related