0

I have records in table like below format.

Table struture

CREATE TABLE test.orders (
id serial NOT NULL,
info json NOT NULL,
value varchar NULL,
CONSTRAINT orders_pkey PRIMARY KEY (id)

);

enter image description here

If I pass value for info column {"101":1} then only value s1 should be fetched not the others.

So is there any feasible way in postges to fetch exact this type of result?

Thanks

2

1 Answer 1

0

If you want those where the value matches exactly the key/value pair, use = with a jsonb type.

select *
from the_table
where info::jsonb = '{"101": 1}'

As your column is not defined as jsonb (which it should be) you have to cast it before you can compare it.

5
  • Question is updated with table structure and contains operator @> supported in postgres verion 10.10?
    – 5a01d01P
    Jan 30, 2020 at 18:33
  • @5a01d01P: yes, Postgres 10 supports @> but only for jsonb so you need to cast your column (as I already mentioned)
    – user1822
    Jan 30, 2020 at 18:40
  • Your query is working but it gives me all records. If I pass '{"101": 1}' then only first row record should be return not other because other records have some additional values.
    – 5a01d01P
    Jan 30, 2020 at 18:44
  • @5a01d01P: as I said that was unclear from your question. See my edit
    – user1822
    Jan 30, 2020 at 18:45
  • Thanks a lot, Now one last thing if I create index on info column then it will work for large record set?
    – 5a01d01P
    Jan 30, 2020 at 18:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.