How do I access child data from a JSON/REST API load?
I've been stuck on this for a decent bit - I'm wondering if any of you may be able to help me. I'm looking to load in JSON data via a REST API call. The data will be loaded in as you can see in the picture below:
In this image, you'll see the fields id, projectID, data, etc. The 'data' field is where all my actual data is as of right now. That field is structured as you can see below:
Above is a semi-formatted view of what the data field has inside of it. You can see towards the bottom "location" is the start of an entirely new tree, and this pattern continues.
If I load the data normally, I can get the high level details of the first picture. From there, I can use the SubField function to get the actual data points I need, however it causes problems when there is a sentence (comma is the delimiter) that is saved as one of the values.
I've seen a lot of "nested" select statements through the forums here, and am currently playing around with statements like below. I've had some pretty serious trouble finding documentation on SQL Select and the various functions at play in this kind of load.
Here's , my current understanding of what I just posted - please let me know how warm I am.
Custom connect allows me to access the database where I am pulling the data, however only the "data" field is a JSON object. I believe that what should happen is the SQL select would loop through each record in the top level DB based on the ID, and for each ID I would have the "nested" attributes (as you can see, I tried to rename the original ID, but it gave me errors for trying that?).
I can get all of the top level data normally, however everything past the 2nd SELECT statement has been blank no matter what I try. Does anyone have any ideas what I may be missing? Thank you!!!