JSON/HSTORE in PostgreSQL Arrays

Question

i am struggling with storing a JSON object into Postgresql Array .. I did something like this --

CREATE TABLE rock_band
(
   name text,
   members VARCHAR(200) []
);

INSERT INTO rock_band VALUES ('Linkin Park','{"Name":"Chester" , "age" : "38"}');

ERROR:  malformed array literal:

Any guidance/pointers on how we can store a json into Postgresql array and then append further json objects to the same array.

Also if we can store a JSON object in Postgresql Array , then can we store an HSTORE value.

--- UPDATE ---

Ok , with the help of one of the guys from heapanalytics , I am able to insert values as Array of JSON Objects

CREATE TABLE rock_band (
  name TEXT,
  members JSON[]  <<--- This is changed now as compared to last one 
);

INSERT INTO rock_band VALUES ('The Who', '{}');

To append to the array i did:

UPDATE rock_band SET members = array_append(members, '{"name":"Roger Daltry", "age":61}')     WHERE name = 'The Who';

Then I get something like this --

select * from rock_band;
  name   |                                     members
---------+----------------------------------------------------------------------------------
 The Who | {"{\"name\":\"Roger Daltry\", \"age\":61}","{\"name\":\"Chester\", \"age\":38}"}
(1 row)

But now the question is how do i query it , something like --

 test=# select members from rock_band where 'Chester' = ANY(members->'name');
 ERROR:  operator does not exist: json[] -> unknown
 LINE 1: ...mbers from rock_band where 'Chester' = ANY(members->'name');
                                                         ^
 HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

Any suggestions ?

You are trying to set the value of `members` to the string `'{"Name":"Chester" , "age" : "38"}'`. As far as Postgres is concerned, there's no JSON here, just a string. You would get the same error with `INSERT INTO rock_band VALUES ('foo', 'bar')`. See [this documentation](http://www.postgresql.org/docs/current/interactive/arrays.html) for how to write an array literal. — IMSoP, Apr 04 '14 at 00:20
This is going to be no fun at all to query and work with. I strongly recommend *normalizing* the data. Just because PostgreSQL supports arrays, json, etc, doesn't mean you should use them as your first preference. — Craig Ringer, Apr 04 '14 at 00:45

ArtemGr · Accepted Answer · 2014-04-04T13:22:05.907

0

I don't really see a problem, it's all in the docs.
Here's an example: http://sqlfiddle.com/#!15/864bf/7

E.g.

SELECT name FROM (SELECT name, members, generate_subscripts (members, 1) AS s FROM rock_band) AS foo
  WHERE members[s]->>'Name' = 'Chester';

edited Apr 04 '14 at 13:22

answered Apr 04 '14 at 13:16

ArtemGr

11,684
3
52
85

This works fine but what if we have many json objects in the array , it makes every object as a row ( inner query ) .. wont it be a performance impact ? – UberNeo Apr 05 '14 at 16:01
Sure it will! If you want a faster JSON (e.g. with an index), you should wait for this: http://obartunov.livejournal.com/177247.html Meanwhile you can use hstore: http://www.postgresql.org/docs/9.3/static/hstore.html#AEN149159 , but not in array ( http://stackoverflow.com/questions/10115152/indexes-on-postgresql-hstore-array-columns ) – ArtemGr Apr 05 '14 at 16:05
You can emulate array in hstore, of course: `"Charlie" => "name.1", "Pete" => "name.2"`. There's also a full-text search. Or you can normalize. Performance wasn't in your question, anyway. – ArtemGr Apr 05 '14 at 16:15
1

FYI: http://michael.otacoo.com/postgresql-2/postgres-9-4-feature-highlight-indexing-jsonb/ – ArtemGr Apr 07 '14 at 17:31

JSON/HSTORE in PostgreSQL Arrays

1 Answers1