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

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

This is a sample row of data:

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';

CodePudding user response：

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:

• What's the proper index for querying structures in arrays in Postgres jsonb?
• Postgres 9.4 jsonb array as table