Forum Discussion

Garrett's avatar
Garrett
New Contributor II
5 years ago
Solved

JSON returns Column Names separately from the Rows

I have a REST API that returns something like the following:

entity":
{
“items”:
[
{
“tableName”:
“mytable”
“count”:
7995
“columnNames”:
[
“c1”,“c2”,“c3”,“c4”
]
“rows”:
[
[r1c1value,r1c2value,r1c3value,r1c4value],[r2c1value,r2c2value,r2c3value,r2c4value],[…],[…],[…],[…],[…],[…],[…],[…]
]
}
]
}

When I refer to jsonPath($, “entity.items[*]”) it gives me columns as an array of columns and the rows are an array of rows.

How can I parse this so that I can map it to a Mapper to put into SQL?

Thanks

sample_pipeline
  • NAl's avatar
    NAl
    4 years ago

    Hi @Spiro_Taleski,

    After attempting lots of variations, this one did the trick:
    'CUSTOM_FIELD__c = TRUE'

    As always thanks for your help

7 Replies

    • Garrett's avatar
      Garrett
      New Contributor II
      5 years ago
      Copy LinkReport

      This looks really good and looks like the solution. I am getting an error and it is probably because I’m slightly off on the object above.

      Failure: Expecting object for JSON-Path field reference ‘entity’, found: null, Reason: Expecting object for JSON-Path field reference ‘entity’, found: null, Resolution: Change the path to refer to an object

      It really is:
      {statusline:{},entity{},headers{}}

      When I refer to anything in there though I have always used $entity as you did so I am not understanding why it isn’t doing the reference properly.

      I added a first splitter to get:
      jsonPath($, “entity.items[*]”) this then let:

      the Mapper Object see columnNames and rows in the Input Schema on the left which when I just connect the JSON output to the mapper there was nothing showing in the Input Schema.

      It looks good now but the Json Splitter isn’t returning anything and hence the mapper. No errors. No output.

  • Siva_Venna's avatar
    Siva_Venna
    Contributor
    5 years ago
    Copy LinkReport

    pull out two arrays separately using a mapper 1) entity.items.columnNames 2) entiy.items.rows and then map the values.

  • Garrett's avatar
    Garrett
    New Contributor II
    5 years ago
    Copy LinkReport

    I got it.

    I had to do the splitter first and get [rows] and [columns]
    then I was able to modify the sl.ZipObject slightly from your original to:
    jsonPath($, “rows[]").map(x=>sl.zipObject(jsonPath($, "columnNames[]”),x))

    It was like it couldn’t reference it from the entity level.

  • Garrett's avatar
    Garrett
    New Contributor II
    5 years ago
    Copy LinkReport

    Ok well… Here’s another issue.
    That export works great as long as the field names are unique in the output of the JSON.
    But if they are the same it fails and there is no way I can think of to remedy that issue.

    so this is really weird but in the API we are calling from it returns subtables inside the output of the parent table example.

    select ,child. from parent

    • gives all the fields of the parent and child.* gives all from the child.
      There is no way that I can think of with that splitter method to name the columns uniquely since I really do need to give them unique names.

    I wouldn’t mind if I was able to get the columns ordinally or have it append some kind of index to the field. say id,id_1,id_2.
    Put I haven’t been able to figure that out.