Postgres: Loop through json array equivalent in SQL?

Question

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.

What do you actually want it to do? Do you have any SQL to start with? — Cory Madden
– Cory Madden, Commented Jul 22, 2017 at 19:41
edit your question and add some sample data and the expected output based on that data. Formatted text please, no screen shots. Do not post code or additional information in comments — user330315
– user330315, Commented Jul 22, 2017 at 20:18

FluxDipole · Accepted Answer · 2017-07-24 09:44:54Z

2

I was looking for jsonb_array_elements(activities)

answered Jul 24, 2017 at 9:44

FluxDipole

314 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Abelisto · Accepted Answer · 2017-07-23 06:28:52Z

0

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;

edited Jul 23, 2017 at 6:28

Abelisto

15.8k3 gold badges38 silver badges47 bronze badges

answered Jul 22, 2017 at 21:46

Brent Snyder

11 bronze badge

Collectives™ on Stack Overflow

Postgres: Loop through json array equivalent in SQL?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related