I have one API that returns nested arrays in a way that I need to extract a parent id before saving to a SQl table. I am not sure if this is possible. I have the following json:

{
  "level1": [
    {
      "id": 1,
      "level2": [
        {
          "key": "comment",
          "value": "Hello"
        },
        {
          "key": "Amount",
          "value": "11"
        }
      ]
    },
    {
      "id": 2,
      "level2": [
        {
          "key": "comment",
          "value": "Good"
        },
        {
          "key": "Amount",
          "value": "22"
        }
      ]
    }
  ]
}

I want to save this to a sql table with the columns id, key, value. So, my goal with the jmespath is to create a structure like this:

[
  {
    "id": 1,
    "key": "comment",
    "value": "Hello"
  },
  {
    "id": 1,
    "key": "Amount",
    "value": "11"
  },
  {
    "id": 2,
    "key": "comment",
    "value": "Good"
  },
  {
    "id": 2,
    "key": "Amount",
    "value": "22"
  }
]

I am not able to get this to work. Do you have a good solution?

OK, i tried too, couldn't manage without new logic. function

explode

will be available next release (EOW). You'll be able to do:

yaml
response:
    ...
    jmespath: "level1[]"

processors:
  - expression: explode(record, "level2")
    output: record

so, technically 1 record becomes many. But processors logic will handle that, and batch properly into the output datastream. https://cdn.discordapp.com/attachments/1470695021601882164/1470769709942046917/image.png?ex=698c8088&is=698b2f08&hm=55861b437613b2c9f1c866e946ab03bc93f704246cd656471bad7804c2c3aaef&

Nice! I was thinking that you could maybe add a general mechanism to pass the data through a pipeline. Doing this transform on the command line with jq is super simple. But your explode solution sounds good as well.

🤔 , that could be great to add. How would you envision the syntax? via a function (like

jq(record, "jq-expr")

?

I was thinking more like shell("jq 'jq-expr'") to allow any kind of shell-based transform. But I have been thinking about this for 2 minutes, I have no idea how this would fit in the general flow.

yeah, calling shell would make it slow. I think adding a native jq function should work great. Will add thanks for suggesting

If adding native support for jq you could maybe even add this on the same level as jmespath, so add a new yaml element on the same level as jmespath. The user could then use either jmespath or jq depending on their preference or need.

yup 💯

By the way: My customer Ellos in sweden will probably buy sling CLI Pro as soon as possible on my recommendation.

ah Ok great, thank you. I did respond to Erik this morning.