Postgres JSON Query within list

Question

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

Kristján · Accepted Answer · 2015-05-28 15:39:13Z

10

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.

edited May 28, 2015 at 15:39

answered May 28, 2015 at 15:27

Kristján

19k5 gold badges55 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

janderson Over a year ago

Wow, such an easy fix. Is there any reason to use the text version? (->> over ->)

Kristján Over a year ago

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.

Collectives™ on Stack Overflow

Postgres JSON Query within list

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related