2

I want to retrieve data by specific field operation it store array of object. i want to add new object in it.

CREATE TABLE justjson ( id INTEGER, doc JSONB);
INSERT INTO justjson VALUES ( 1, '[
  {
    "name": "abc",
    "age": "22"
  },
  {
    "name": "def",
    "age": "23"
  }
]');

retrieve data where age is greater then and equal to 23 how is possible

And i have solution for such thing but it decrease query performance to much.

my solutions is using jsonb_array_elements:

t=# with a as (select *,jsonb_array_elements(doc) k from justjson)
select k from a where (k->>'age')::int >= 23;
              k
------------------------------
 {"age": "23", "name": "def"}
(1 row)

I need a solution or other thing by which i do such thing with high performance.

Sandip Mavani
  • 120
  • 2
  • 5
  • "*by which i do such thing with high performance*" - normalize your tables, then this can be indexed efficiently and the query becomes much simpler as well –  Oct 30 '17 at 08:15
  • @a_horse_with_no_name in this i have billion of record and using gin(jsonb_path_ops) i am perform only json equal to like operation EX select * from justjson WHERE justjson @> '[ { "age":"23"]'; in this got response in ms but when perform above query it performance degrade . – Sandip Mavani Oct 30 '17 at 08:30
  • The [note no native arrays](https://www.postgresql.org/docs/current/static/arrays.html) in the manual is just as true for JSON and even more so for arrays inside JSON: "*Arrays are not sets; searching for specific array elements can be a sign of database misdesign*" –  Oct 30 '17 at 08:33

0 Answers0