Accessing Data in many nested JSON arrays

💬 Queries and Resources
1

Hey there,

I have seen a few posts about accessing values in nested json arrays but so far I have had no luck applying the solutions against my JSON output.
Could anyone assist me in extracting:
First Name
Last Name
Email Address
from this piece of JSON?

{
    "data": [
        {
            "tables": [
                {
                    "defaultTTL": 157680000,
                    "fields": [
                        {
                            "name": "key",
                            "type": "text",
                            "value": "d3cb774f-6691-49ce-ba3c-010450e7dd88"
                        },
                        {
                            "name": "id",
                            "ttl": "147396962",
                            "type": "text",
                            "value": "d3cb774f-6691-49ce-ba3c-010450e7dd88",
                            "writetime": "2023-05-03T13:02:26.477Z"
                        },
                        {
                            "name": "applicationstatus",
                            "ttl": "143673084",
                            "type": "text",
                            "value": "Incomplete",
                            "writetime": "2023-03-21T10:37:48.251Z"
                        },
                        {
                            "name": "birthdate",
                            "ttl": "142395951",
                            "type": "text",
                            "value": "06/06/1983",
                            "writetime": "2023-03-06T15:52:15.923Z"
                        },
                        {
                            "name": "category",
                            "ttl": "147396962",
                            "type": "text",
                            "value": "homepage",
                            "writetime": "2023-05-03T13:02:26.477Z"
                        },
                        {
                            "name": "contactpref",
                            "ttl": "143673084",
                            "type": "text",
                            "value": "Email",
                            "writetime": "2023-03-21T10:37:48.251Z"
                        },
                        {
                            "name": "country",
                            "ttl": "142395951",
                            "type": "text",
                            "value": "United Kingdom",
                            "writetime": "2023-03-06T15:52:15.923Z"
                        },
                        {
                            "name": "dretimestamp",
                            "type": "text"
                        },
                        {
                            "name": "emailaddress",
                            "ttl": "143673084",
                            "type": "text",
                            "value": "not@home.com",
                            "writetime": "2023-03-21T10:37:48.251Z"
                        },
                        {
                            "name": "employmentstatus",
                            "ttl": "142395951",
                            "type": "text",
                            "value": "Employed",
                            "writetime": "2023-03-06T15:52:15.923Z"
                        },
                        {
                            "name": "event",
                            "ttl": "147396962",
                            "type": "text",
                            "value": "pageview",
                            "writetime": "2023-05-03T13:02:26.477Z"
                        },
                        {
                            "name": "firstname",
                            "ttl": "143673084",
                            "type": "text",
                            "value": "John",
                            "writetime": "2023-03-21T10:37:48.251Z"
                        },
                        {
                            "name": "grossincome",
                            "ttl": "142395951",
                            "type": "text",
                            "value": "25,000 - 40,000",
                            "writetime": "2023-03-06T15:52:15.923Z"
                        },
                        {
                            "name": "lastname",
                            "ttl": "143673084",
                            "type": "text",
                            "value": "Smith",
                            "writetime": "2023-03-21T10:37:48.251Z"
                        },
                        {
                            "name": "lastviewedpage",
                            "ttl": "147396962",
                            "type": "text",
                            "value": "finance - home",
                            "writetime": "2023-05-03T13:02:26.477Z"
                        },
                        {
                            "name": "lastviewedproduct",
                            "ttl": "143673074",
                            "type": "text",
                            "value": "blue credit card",
                            "writetime": "2023-03-21T10:37:38.931001Z"
                        },
                        {
                            "name": "phonenumber",
                            "ttl": "143673084",
                            "type": "text",
                            "value": "078910111213",
                            "writetime": "2023-03-21T10:37:48.251Z"
                        },
                        {
                            "name": "productid",
                            "ttl": "143673084",
                            "type": "text",
                            "value": "blue credit card",
                            "writetime": "2023-03-21T10:37:48.251Z"
                        },
                        {
                            "name": "residencetype",
                            "ttl": "142395951",
                            "type": "text",
                            "value": "Detached",
                            "writetime": "2023-03-06T15:52:15.923Z"
                        },
                        {
                            "name": "streetaddress",
                            "ttl": "142395951",
                            "type": "text",
                            "value": "88 Street",
                            "writetime": "2023-03-06T15:52:15.923Z"
                        },
                        {
                            "name": "timestamp",
                            "ttl": "147396962",
                            "type": "text",
                            "value": "2023-05-03T13:02:26.387 UTC",
                            "writetime": "2023-05-03T13:02:26.477Z"
                        },
                        {
                            "name": "visitorid",
                            "ttl": "147396962",
                            "type": "text",
                            "value": "7cab9748-228a-4bab-8650-06a769ee51f1",
                            "writetime": "2023-05-03T13:02:26.477Z"
                        }
                    ],
                    "key": "key",
                    "name": "userdetails",
                    "type": "normal"
                }
            ],
            "target": "dev"
        }
    ],
    "status": "OK"
}

Many thanks!

2

We don't know where you want to put this data. In a table or in a text? If you explain exactly what you want to do with this data, I'll try to help.

3

Hi @valentino, welcome to the community :hugs:

If you're asking about HOW to do that here's a little script:

const data = yourJSONObject
const extractFields = (fields, targetFields) => fields
  .filter(({ name }) => targetFields.includes(name))
  .reduce((acc, { name, value }) => ({ ...acc, [name]: value }), {});

const targetFields = ["firstname", "lastname", "emailaddress"];
const fields = data.data[0].tables[0].fields;
const extractedData = extractFields(fields, targetFields);
const { firstname, lastname, emailaddress } = extractedData;

return extractedData

Hope that helps, let us know if you need something more specific

2 Likes
4

Thanks for the reply! I was just looking to display the values in simple text fields to begin.

I have a text entry box where a enduser would enter a user id, hit a search button and then it would query my API and bring back the user details in simple text fields below

5

Hi @minijohn Thanks for the warm welcome to the community, today is day 0 for me in using Retool! Lots to learn but I am excited for the journey!
The solution you have provided does exactly what I need, so thanks so much for taking the time to help me out.

3 Likes
6

Aah the early days... Feel free to reach out whenever :))

3 Likes
7

Thanks for posting @valentino and thanks @minijohn and @edurmush for chiming in to help!

I'll go ahead and close this out since there's a Solution marked that worked! :tada: Feel free to create new topics as you come across other things you'd like advice on as you start your building journey with Retool :slight_smile:

1 Like