Postgres check if value exists in a table's column

Question

How would I check to see if a value exists with name Dan in column sess from a table called Table1 with 3 columns called id, sess, and timeD.

Sess type is JSON

Table1

id    sess                                         timeD
1     {"cookie":{expires:null}, "name": "Joey"}    12:30
2     {"cookie":{expires:null}, "name": "Dan"}      1:00
3     {"cookie":{expires:null}, "name": "Bob"}      1:20

@bca I'm checking to see if name has value Dan in the sess column. I'm not checking if the column is null — guest99231
– guest99231, Commented Sep 7, 2021 at 18:41
@jarlh I'm reading it but like how would you determine the name is Bob? — guest99231
– guest99231, Commented Sep 7, 2021 at 18:54
Generally you should provide the relevant schema (columns and types), indexes, postgres version, etc. — melcher
– melcher, Commented Sep 7, 2021 at 19:13

melcher · Accepted Answer · 2021-09-07 19:19:04Z

0

This depends on the column type of sess. If it's a string you can use a string match, e.g.

where sess like '%"name": "Dan"%'

If it's json you can query for values inside the json object using JSON functions and operators, e.g.

where sess->>'name' = 'Bob'

Or if it's a string type but contains JSON you can parse the string to JSON then query on the JSON like:

where sess::json->>'name' = 'Bob'

answered Sep 7, 2021 at 19:19

melcher

1,60110 silver badges15 bronze badges

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

Comments

user330315 · Accepted Answer · 2021-09-07 21:16:17Z

0

You can use the contains operator: @>

where sess @> '{"name": "Dan"}'

this assumes that sess is defined as a jsonb column (which it should be). If it's not, you need to cast it: sess::jsonb @> ...

answered Sep 7, 2021 at 21:16

user330315

Collectives™ on Stack Overflow

Postgres check if value exists in a table's column

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related