Using Postgres JSON Functions on a table row

Question

I'm stuck with this, so maybe someone can help me with it. I have a table "answers" with two columns id and json_data. In each row of json_data column I have saved object data which looks like this:

firs json_data row:

{
    "question161839": {"answer": "test1 answer", "review_answ": "true"}, 
    "question161840": {"answer": "test2 answer", "review_answ": "false"}, 
    "question161841": {"answer": "test3 answer", "review_answ": "true"}, 
}

next json_data row:

{
    "question161850": {"answer": "test4 answer", "review_answ": "true"}, 
    "question161851": {"answer": "test5 answer", "review_answ": "true"}, 
    "question161852": {"answer": "test6 answer", "review_answ": "false"}, 
    "question161853": {"answer": "test7 answer", "review_answ": "false"}
}

I'm looking for Postgres query, which allows me to select a questions where "review_answ" is false. Something like:

select * from answers where json_data->review_answ = false

and as a results I'm expecting:

question161840, question161852, question161853

I Found a similar problem here but I don't understand how to use it in my case.

Do you want the results in multiple rows, or a single row? Which Postgres version are you using? — user330315
– user330315, Commented Apr 15, 2021 at 8:53
If it's possible I'll prefer multiple rows and I'm using Postgres 11.8 — Quente
– Quente, Commented Apr 15, 2021 at 11:33

user330315 · Accepted Answer · 2021-04-15 11:55:54Z

1

You can extract all key/value pairs using jsonb_each() the result of that can be filtered using a WHERE condition:

select a.id, t.questionid
from answers a
  cross join jsonb_each(a.json_data) t(questionid, details)
where t.details ->> 'review_answ' = 'false'
order by a.id;

If your column is defined as json rather than jsonb (which it should be), you need to use json_each() instead.

Online example

answered Apr 15, 2021 at 11:55

user330315

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

Comments

Noerone · Accepted Answer · 2021-04-15 09:40:23Z

-1

maybe like this :

select substring(json_data::TEXT,4,14) from answers where json_data ->> review_answ = false

edited Apr 15, 2021 at 9:40

answered Apr 15, 2021 at 9:17

Noerone

262 bronze badges

3 Comments

Quente Over a year ago

Thanks but nope, it's not working: Query 1 ERROR: ERROR: column "review_answ" does not exist

Noerone Over a year ago

You only need to change the conditions where like this: SELECT substring(json_data::TEXT,4,14) FROM answers WHERE json_data::TEXT LIKE '%"review_answ": "false"%'

Quente Over a year ago

Thank you for your help - this solution works too!

Collectives™ on Stack Overflow

Using Postgres JSON Functions on a table row

2 Answers 2

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related