Postgresql query by json key condition

Question

I've a table with JSON column and want to select rows where JSON key 'k' has value 'value'. Json may consist of several pairs of [K,V].

[
  {"k":"esr:code","v":"800539"},
  {"k":"lit","v":"yes"},
  {"k":"name","v":"5 км"},
  {"k":"railway","v":"halt"},
  {"k":"uic_ref","v":"2040757"}
]

I tried to use the next query, but it's wrong.

SELECT * 
FROM public.node 
where ((node.tags)::json->>'k' like 'name')

How I can fix it, if it's possible?)

Where node - table name, tags - json column.

Why isn't tags defined as jsonb (or at least json) if you store a JSON value in it? — user330315
– user330315, Commented Sep 6, 2022 at 8:59

user330315 · Accepted Answer · 2022-09-06 09:02:58Z

1

You can use the JSONB contains operator @>

SELECT * 
FROM public.node 
where node.tags @> '[{"k","name"}]';

This will do an exact match against name. Your usage of like might indicate you are looking for a partial match - however as your like condition doesn't use a wildcard it's the same as =.

This assumes that tags is defined as jsonb (which it should be). If it's not you need to cast it: node.tags::jsonb

answered Sep 6, 2022 at 9:02

user330315

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

Collectives™ on Stack Overflow

Postgresql query by json key condition

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