SELECT by json array value

Question

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

name (varchar)
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 · Accepted Answer · 2022-11-20 00:54:25Z

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:

edited Nov 20, 2022 at 0:54

answered Nov 20, 2022 at 0:19

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

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

Collectives™ on Stack Overflow

SELECT by json array value

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related