Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.

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 ?

share|improve this question
    
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 for how to write an array literal. –  IMSoP Apr 4 '14 at 0:20
2  
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 4 '14 at 0:45

1 Answer 1

up vote 0 down vote accepted

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';
share|improve this answer
    
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 5 '14 at 16:01
    
Sure it will! If you want a faster JSON (e.g. with an index), you should wait for this: obartunov.livejournal.com/177247.html Meanwhile you can use hstore: postgresql.org/docs/9.3/static/hstore.html#AEN149159 , but not in array ( stackoverflow.com/questions/10115152/… ) –  ArtemGr Apr 5 '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 5 '14 at 16:15
1  
FYI: michael.otacoo.com/postgresql-2/… –  ArtemGr Apr 7 '14 at 17:31
    
FYI: obartunov.livejournal.com/177977.html –  ArtemGr Apr 9 '14 at 17:14

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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