Query JSON with SQL - Selecting specific array values

[Bre]

I am querying the following data:

[{
        "id": "post_83eb1f2e-6ac4-4ac0-9058-bf1a708743de",
        "title": "Post Title 1",
        "stats": {
            "email": {
                "recipients": 0
            },
            "web": {
                "views": 0,
                "clicks": 0
            },
            "clicks": []
        }
    }, {
        "id": "post_08550733-28c5-4ceb-a13e-6b5bc960708f",
        "title": "Post Title 2",
        "stats": {
            "email": {
                "recipients": 100
            },
            "web": {
                "views": 0,
                "clicks": 0
            },
            "clicks": [{
                    "url": "https://www.instagram.com/somethingorother/?utm_campaign=generic-auto-campaign-value",
                    "total_clicks": 39
                }, {
                    "url": "https://go.testdomain.com/test?utm_source=em-en&utm_medium=e&utm_campaign=thecampaign&utm_id=896",
                    "total_clicks": 54
                },
            ]

        }
    }
]

and attempting to get out

id, title, campaign
post_83eb1f2e-6ac4-4ac0-9058-bf1a708743de,Post Title 1,NULL
post_08550733-28c5-4ceb-a13e-6b5bc960708f,Post Title 2,thecampaign

for URL I am looking to parse out the utm_campaign of the first go.testdomain.com url in the clicks array.. if present but can't get to even getting the full URL.

I've tried understanding and using the AlaSQL SEARCH syntax but so far no luck. Any ideas would be much appreciated!

[pyrrho]

You could send this to a transformer as per this post's suggestion to filter/parse the data with JS map/filters:

Select data from JSON in nested data

Hey @NickJ, welcome to the community Couldn't get it to work with AlaSQL (which is what the "Query JSON with SQL" query uses). Try a transformer in the meantime: const output = data.map(x => { return { name: x.name, age: x.age, roles: x.roles.map(x => {return x.name}).join(", ") } }) return output

This also seems to be somewhat achievable using the ARRAY function in AlaSQL but you might have to join two separate queries to get the results you want.

[Darren]

Hi @Bre! Welcome back to the community.

I'm also not sure how to do this with AlaSQL, but it's fairly straightforward with a transformer:

This is probably the more straightforward approach, given the fact that it requires some filtering and string parsing. Let me know if you have any questions!