Postgresql query by json key condition-CodePudding

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.

CodePudding user response：

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