CODEWITHSUNDEEP
jsonpostgresql
janderson
janderson

Reputation: 973

Postgres JSON Query within list

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"}]}]"

Upvotes: 2

Views: 10181

Answers (1)

Kristján
Kristján

Reputation: 18813

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.

Upvotes: 10

Related Questions