1

I have a table named "games" with 2 fields:

  1. name (varchar)
  2. data (json)

This is a sample row of data:

name data
Test {"players":["PlayerOne","PlayerTwo"],"topPlayers":["PlayerTen","PlayerThirteen"]}

Now I want to SELECT rows which have a "player" named "PlayerOne".

I've tried following SQL commands without success:

SELECT * FROM games WHERE data -> players = 'PlayerOne';

SELECT * FROM games WHERE data ->> players = 'PlayerOne';

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
H So
  • 13
  • 4

1 Answers1

1

The position of the array element won't be the same every time, so the direct reference you tried doesn't work - even after fixing your expression: data -> 'players' ->> 0 or data #>> '{players,0}'

Use the data type jsonb instead of json, and the jsonb containment operator @>:

SELECT *
FROM   games
WHERE  data @> '{"players":["PlayerOne"]}';

If you can't change the table definition, add a cast in the query:

...
WHERE  data::jsonb @> '{"players":["PlayerOne"]}';

Either way, if the table is big, you want to support this with an index - an expression index in the latter case. See:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228