how to count all the keys in json array in postgresql

Question

I have a table in which I have form id and the form fields column

=======================
form_id | form_fields
=======================
  1     | [abc,def]

Now there is one other table that contains the submitted form's response data like this

================================
device_id | form_id | response
================================
  101     |    1    | {abc:true}
================================
  102     |    1    | {def:true}
================================
  103     |    1    | {def:true}
================================
  101     |    2    | {xyz :true}

I want to get all the count of array values of form_fields of form_id 1 in the first table from the second table. I mean result be like

{abc:1,def:2}

One way is to make a simple json of all the responses then iterate one by one object of json and check the values. Is there any other efficient and better way to solve this? I am new to postgres so please ignore if this is too basic to ask.

Thanks!

The COUNT function returns the number of rows that match a specific condition of a query — gkhaos
– gkhaos, Commented Apr 15, 2018 at 17:33

gkhaos · Accepted Answer · 2018-04-15 17:38:38Z

1

I'm not really sure about this {abc:true} since i only work with values in columns, not key_value pairs, so please correct me if i'm wrong

but if they are also just values (like a varchar/...) this should work

select count(*) from your_table where form_id = 1 and response = 'abc'

select count(*) from your_table where form_id = 1 and response = 'def'

answered Apr 15, 2018 at 17:38

gkhaos

7059 silver badges20 bronze badges

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

Collectives™ on Stack Overflow

how to count all the keys in json array in postgresql

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