2

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"}]}]"
janderson
  • 963
  • 4
  • 14
  • 26

1 Answers1

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.

Kristján
  • 18,165
  • 5
  • 50
  • 62
  • Wow, such an easy fix. Is there any reason to use the text version? (->> over ->) – janderson May 28 '15 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](http://sqlfiddle.com/#!15/4ba26/3). – Kristján May 28 '15 at 15:35