Name is required.
Email address is required.
Invalid email address
Answer is required.
Exceeding max length of 5KB

JSON transformations

Hii below is my sample jSON
i am trying to transform this into table , using manual jsonpaths file. . but getting error "invalid JSON path , member is not valid object" . How to work with nested jSON which as array of elements inside main element?

in below example , how to access elements inside array B ?
my columns should be A, a , c , e

{

"A": value,

"B": [
{
"A1": {
"a": value,
"b": value
},
"c": value,
"d": value,
"e": {
value
}

any complex examples of nested JSON transformations ?

1 Community Answers

Matillion Agent  

Ian Funnell —

Hi Sanket,

How many records are you expecting in the final table?

If one record then you should be able to use a JSONPaths file.
If multiple records then you can’t achieve it with the COPY command. A JSONPaths file is not sufficient to flatten nested arrays.

Two good ways of flatten nested arrays are:

  1. During data loading using Matillion’s Nested Data Load Component
  2. During data loading using the Athena JDBC driver for example as shown in this article on the AWS Big Data Blog

Best regards,
Ian

Post Your Community Answer

To add an answer please login