Flatten or Filter Nested JSON

I have a restAPI response with lots of lovely nested Arrays like the example below.

  "synopses": [
    {
      "source": {
        "id": "321",
        "value": "ProviderX"
      },
      "locked": false,
      "descriptions": [
        {
          "lang": "en",
          "value": "Only ten men can solve the mystery of the murdered French football coach and retrieve the priceless Pink Panther diamond.",
          "length": "LONG"
        },
        {
          "lang": "en",
          "value": "Only ten men can solve the mystery of the murdered French football coach.",
          "length": "SHORT"
        }
      ]
    }
  ],
    {
      "source": {
        "id": "123",
        "value": "ProviderY"
      },
      "locked": false,
      "descriptions": [
        {
          "lang": "en",
          "value": "Only one man can solve the mystery of the murdered French football coach and retrieve the priceless Pink Panther diamond.",
          "length": "LONG"
        },
        {
          "lang": "en",
          "value": "Only one man can solve the mystery of the murdered French football coach.",
          "length": "SHORT"
        }
      ]
    }
  ]
}

Ultimately I'd like to have a table of the $.synopses[*].description fields for each of the values of $.synopses[*].source.value.

I can reference the first synopses with a query JSON with SQL query like so, but how can I filter on $.synopses[*].source.value for each of the tables? Is there a where clause syntax that allows me to move back up the JSON path?

select * from 
{{query1.data.synopses[0].descriptions}}

Hi @Brad! Welcome to the community! :sunglasses:

Are you looking to have multiple rows for each of the synopses.values (one for each of the descriptions), or have many description columns for each synopses.value?

1 Like

Hi Ben, I am looking to have multiple rows for each of the synopses. It's the filtering on source that I can't get my head around.

Something like:
sample

@Brad

I see! Yes this is tricky, here's an example which might help get you started:

You could also use JS to parse this:

Hopefully this helps!

1 Like

Hello!

I previously used this format (arr->key) to pull out values from an array within my query but recently it stopped pulling the data through correctly

These are the steps that I'm following:

  1. I have a MongoDB query that returns the following sample data as an example, lets assume there is a string column called companyName and a json columns called 'list_admin' which has the below format
[
{"id": 1,
"email": test1@test.com},
{"id": 4,
"email": test4@test.com}
]
  1. I was then using Query JSON with SQL to return the email addresses so they showed in a concatenated list for each company
select
companyName,
GROUP_CONCAT(list_admin -> email) 
from {{getCompanies.data}}

group by companyName

Previously this returned the following table

Company Name | test1@test.com, test4@test.com

But now I'm getting the error attached

Any help much appreciated!

Hey @vicdavies01!

Super strange that it stopped working. If you check the State tab in the left panel, is the query.data property the same as before (an array of objects)?

Also, if you’re just trying to return an array of the emails, you should be able to do this with some JS, like:

{{ formatDataAsObject(getCompanies.data).email }}

Or if you want to return it in a data structure that the table component accepts (array of objects or object of arrays), you can do:

{{ {companyName: formatDataAsObject(getCompanies.data).email} }}

Let me know if that would work until we figure out why your setup suddenly stopped working!

I am not able to get my query to flatten a nested array with a similar JS. Hopefully you can help point me in the right direction.

Hey @Trying! How would you like your custom column to look? Where is your custom column data coming from? Are you trying to show just the size property, for example?