CODEWITHSUNDEEP
sqljsonpostgresql
FluxDipole
FluxDipole

Reputation: 31

Postgres: Loop through json array equivalent in SQL?

I am using postgres 9.6.3 and need to convert the following python code to a sql query:

data = response.json()
activities = data['Response']['data']['activities']
for activity in activities:
    activityHash = int(activity['activityHash'])
    if activityHash == 2659248071:
        clears = int(activity['values']['activityCompletions']['basic']['value'])

The table has two columns: (membershipid integer primary key, data jsonb). I am not sure how to handle an array like this in sql. The array is variable length and might or might not include an entry where activityHash == the desired value.

The desired result from the query would be something like SELECT membershipid, clears FROM table.

Upvotes: 1

Views: 2028

Answers (2)

FluxDipole
FluxDipole

Reputation: 31

I was looking for jsonb_array_elements(activities)

Upvotes: 2

Brent Snyder
Brent Snyder

Reputation: 1

I recommend you check out this link that walks you through how to traverse JSONB in Postgres.

Try the following query and see if that works for you:

SELECT
membershipid,
'data' -> 'activity' -> 'response' -> 'data' -> 'activities' ->> 'activityHash' AS activityHash,
'data' -> 'activity' -> 'response' -> 'data' -> 'activities' -> 'activityHash' -> 'values' -> 'activityCompletions' -> 'basic' ->> 'value' AS clears

FROM yourtablename

WHERE
('data' -> 'activity' -> 'response' -> 'data' -> 'activities' ->> 'activityHash')::int = 2659248071;

Upvotes: 0

Related Questions