querying json object from table in postgreSQL

Question

I want to use where condition on a json object in a table, in postgreSql. how i need to do this for example: i have a table 'test' it has three columns name(varchar),url(varchar),more(json). i need to retrive date where css21Colors = Purple. more is a json type and below is the values of more field.

Please let me know what should be syntax of querying for the same?

more = {"colorTree":{"Purple":[{"Spanish Violet":"#522173"}],
"Brown":[{"Dark Puce":"#4e3347"}],"White":[{"White":"#ffffff"}],
"Black":[{"Eerie Black":"#1d0d27"}],"Gray":[{"Rose Quartz":"#a091a4"}]},
"sizeoutscount":0,"css21Colors":{"Purple":69,"Brown":5,"White":4,"Black":17,"Gray":3},
"sizeins": [],"sizeinscount":0,"sizeouts":[],"allsizes":["8","10","16"],
"css3Colors": {"Rose Quartz":3,"White":4,"Dark Puce":5,"Eerie Black":17,"Spanish
Violet":69},"hexColors":{"#522173":69,"#4e3347":5,"#ffffff":4,"#1d0d27":17,"#a091a4":3}}

Mention PostgreSQL version (select version())

Vivek S.
– Vivek S.

2015-04-24 08:26:33 +00:00
Commented Apr 24, 2015 at 8:26 — Vivek S.
– Vivek S., Commented Apr 24, 2015 at 8:26
PostgreSQL version is 9.4.0

singh
– singh

2015-04-24 08:58:10 +00:00
Commented Apr 24, 2015 at 8:58 — singh
– singh, Commented Apr 24, 2015 at 8:58

Kouber Saparev · Accepted Answer · 2015-04-24 10:37:44Z

1

SELECT more->'css21Colors'->'Purple' FROM test;

Additionally you can query only the rows containing that key.

SELECT
  more->'css21Colors'->'Purple'
FROM
  test
WHERE
  (more->'css21Colors')::jsonb ? 'Purple';

Mind switching to the jsonb data type.

edited Apr 24, 2015 at 10:37

answered Apr 24, 2015 at 10:32

Kouber Saparev

8,2552 gold badges32 silver badges28 bronze badges

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

Collectives™ on Stack Overflow

querying json object from table in postgreSQL

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related