Hello! Been trying to solve an issue around nested json restapi response, and hoping I can freeload off someone elses brain who may have a way to handle this scenario. Just getting into Tap Development and have not encountered this scenario yet. I have a source, and can get the response. The response has multiple nesting layers. In the SQL Target, I would require some of this nesting to be a row per json item from the response. Mocked a screenshot, hopefully it makes sense. Screenshot shows very crude mockup of API Response, the needed de-structuring, and the final target-DB result table concept. In addition to transposing/un-nesting the response, due to a lack of nested detail, I also believe I would need to include the higher level ID in each nested object, so in the target DB, it can be related back together.

There are some taps that would just give you the flat record, leaving

messages

as a json entry. Tap tiktok is one, as is google ads. This does mean the downstream unpacking work has to be done by your transformation i.e. dbt or similar. I think it's a personal thing of whether you want your tap to do more or less. I like my tap to fairly simple then I can handle the downstream transformation in sql personally. Also, some taps like

target-postgres

wise version support automatic flattening (which may or may not be to your taste).

Yeah I'd probably just leave it in one stream and let the transformation handle it. You can split it for sure (I think I'd just pass it via context to the child streams and in get_records just return the slice of the data I wanted in each) It's just much easier to understand those mappings at the transformation layer and when folks do it for you you end up hopping back and forth between transformation logic and tap logic (Ideally you can just stay in transformation mode when that's the mode you're in)

(I think I'd just pass it via context to the child streams and in get_records just return the slice of the data I wanted in each)

Genius, why did I think that a child stream needed to be a new API call?!

😄 not genius but we all have good thoughts once in a while. Happy to share 😄

Thank you @visch and @Andy Carter for the insights! I didn't realize it was appropriate to drop json like that into a DB and parse it as a follow-on step with DBT or something. Flatten capabilities are already present, so we can experiment there, and then see what we can learn on the DBT unpacking process. On the other side, I had seen in the docs Parent-Child support, but didnt know enough about what that actually meant yet. Will also look into this process. Regardless of this specific API, I am sure this will come up again and again in the future, so both of these approaches are great paths to go look more into!