postgresql return the count of jsonb objects

Question

I would like to get the count of all products under a certain id.

I tried:

SELECT
    count(jsonb_object_keys(data #> '{products}')) as numProducts 
FROM
    appointment_intakes 
WHERE
    appointment_intakes.id = 'VGDKMjdxn'

I get:

ERROR:  set-valued function called in context that cannot accept a set

When I run the above without the count function, I get a list of keys.

Any idea what I am doing wrong?

Thank you.

I'm not a 100% on this one but I don't believe that is how you use the select count in Postgres... Usually it would be something like Select count(*).. — ryekayo
– ryekayo, Commented Oct 9, 2015 at 13:05

klin · Accepted Answer · 2015-10-09 13:12:10Z

2

Set-valued function has to be called in FROM clause. Use lateral join:

SELECT
    count(keys) as numProducts 
FROM
    appointment_intakes, 
    LATERAL jsonb_object_keys(data #> '{products}') keys
WHERE
    appointment_intakes.id = 'VGDKMjdxn'

answered Oct 9, 2015 at 13:12

klin

123k15 gold badges240 silver badges262 bronze badges

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

Collectives™ on Stack Overflow

postgresql return the count of jsonb objects

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related