current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
stack overflow careers
Sign up ×
Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute:
up vote 2 down vote favorite

I have the following data in a matches table:

{"Id":1,"Teams":[{"Name":"TeamA","Players":[{"Name":"AAA"},{"Name":"BBB"}]},{"Name":"TeamB","Players":[{"Name":"CCC"},{"Name":"DDD"}]}],"TeamRank":[1,2]}

{"Id":2,"Teams":[{"Name":"TeamA","Players":[{"Name":"CCC"},{"Name":"BBB"}]},{"Name":"TeamB","Players":[{"Name":"AAA"},{"Name":"DDD"}]}],"TeamRank":[1,2]}

Ultimately I want to get a list of unique team names.

Following This Question I have been trying to access the Team Name property using the following:

SELECT json_array_elements(match->>'Teams') FROM matches

This returns

ERROR: function json_array_elements(text) does not exist
SQL state: 42883
Hint: No function matches the given name and argument types. You might need to add explicit type casts.
Character: 1560

I have also tried json_array_elements(match->>'Teams'::json) and json_array_elements(to_json(match->>'Teams')) to no avail.

But the following query

SELECT match->>'Teams' FROM matches;

Returns

"[{"Name":"TeamA","Players":[{"Name":"AAA"},{"Name":"BBB"}]},{"Name":"TeamB","Players":[{"Name":"CCC"},{"Name":"DDD"}]}]"
"[{"Name":"TeamA","Players":[{"Name":"CCC"},{"Name":"BBB"}]},{"Name":"TeamB","Players":[{"Name":"AAA"},{"Name":"DDD"}]}]"
share|improve this question

1 Answer 1

up vote 3 down vote accepted

The ->> operator gives you the result as text, but you want it to remain json. Use -> which gives you the json value.

Ref: http://www.postgresql.org/docs/9.4/static/functions-json.html

I believe the order of operations on json_array_elements(match->>'Teams'::json) converts Teams to json before it runs ->>. json_array_elements((match->>'Teams')::json) should work, but is just a roundabout version of ->.

to_json(match->>'Teams') converts to text and then gives you that text as a json object. It doesn't parse the text back into json.

share|improve this answer
    
Wow, such an easy fix. Is there any reason to use the text version? (->> over ->) – janderson May 28 at 15:28
1  
Sometimes you want text, sometimes you want more JSON, totally depends on your use case. There's a little bit of different behavior when NULL is involved - here's a demo. – Kristján May 28 at 15:35

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.