Join to tables with stripe api response

[rensheng_he]

like to join two tables with stripe api response. i am stuck on how to set the condition "On" where subscription has the product id but it is in nested array. so how can i use that data

[WidleStudioLLP]

Hello @rensheng_he Welcome to the Retool Community,

In Retool, if the product ID is nested inside an array within the Stripe API response, you’ll need to use the UNNEST() function or JSON functions to extract it before performing the JOIN.

1. Identify where the product ID is stored

• From your Stripe API response, the product ID is likely inside the items array of the subscriptions object.

2. Extract the product ID before the join

• Use CROSS JOIN UNNEST() if the structure allows it, or JSON functions like json_each().

Example Query:

JOIN 
  (SELECT id, name FROM {{ getProduct.data }}) AS pro
ON 
  pro.id = (
    SELECT json_extract(value, '$.price.product')
    FROM json_each(sub.items)
  )

[Tess]

Hi @rensheng_he Did you sort this out already? I know this query type, Query JSON with SQL, has some syntax limitations because it uses alasql

Another option could be writing some Javascript inside {{getSubscription.data}} to flatten the data you're working with