Return to Answer

added 178 characters in body

edited Nov 14, 2017 at 16:35

65.8k
50
263
512

You're using the wrong operator, you want @> (added in 9.4). ?& only operates over JSON objects.

SELECT j, j @> '"dog"'::jsonb AS hasDog, j @> '["dog","waffle"]' AS hasDogAndWaffle, j @> '5' AS has5, j @> '42' AS has42 FROM ( VALUES ('[5,2,3]'::jsonb), ('["dog","cat","elephant","waffle"]'::jsonb) ) AS t(j); j | hasdog | hasdogandwaffle | has5 | has42 --------------------------------------+--------+-----------------+------+------- [5, 2, 3] | f | f | t | f ["dog", "cat", "elephant", "waffle"] | t | t | f | f (2 rows)

If all you're storing is numbers, you should consider using intarray and not jsonb. It should be a lot faster.

You're using the wrong operator, you want @> (added in 9.4). ?& only operates over JSON objects.

SELECT j, j @> '"dog"'::jsonb AS hasDog, j @> '["dog","waffle"]' AS hasDogAndWaffle, j @> '5' AS has5, j @> '42' AS has42 FROM ( VALUES ('[5,2,3]'::jsonb), ('["dog","cat","elephant","waffle"]'::jsonb) ) AS t(j); j | hasdog | hasdogandwaffle | has5 | has42 --------------------------------------+--------+-----------------+------+------- [5, 2, 3] | f | f | t | f ["dog", "cat", "elephant", "waffle"] | t | t | f | f (2 rows)

You're using the wrong operator, you want @> (added in 9.4). ?& only operates over JSON objects.

SELECT j, j @> '"dog"'::jsonb AS hasDog, j @> '["dog","waffle"]' AS hasDogAndWaffle, j @> '5' AS has5, j @> '42' AS has42 FROM ( VALUES ('[5,2,3]'::jsonb), ('["dog","cat","elephant","waffle"]'::jsonb) ) AS t(j); j | hasdog | hasdogandwaffle | has5 | has42 --------------------------------------+--------+-----------------+------+------- [5, 2, 3] | f | f | t | f ["dog", "cat", "elephant", "waffle"] | t | t | f | f (2 rows)

If all you're storing is numbers, you should consider using intarray and not jsonb. It should be a lot faster.

Source Link

answered Mar 15, 2017 at 16:13

Evan Carroll

65.8k
50
263
512

You're using the wrong operator, you want @> (added in 9.4). ?& only operates over JSON objects.

SELECT j, j @> '"dog"'::jsonb AS hasDog, j @> '["dog","waffle"]' AS hasDogAndWaffle, j @> '5' AS has5, j @> '42' AS has42 FROM ( VALUES ('[5,2,3]'::jsonb), ('["dog","cat","elephant","waffle"]'::jsonb) ) AS t(j); j | hasdog | hasdogandwaffle | has5 | has42 --------------------------------------+--------+-----------------+------+------- [5, 2, 3] | f | f | t | f ["dog", "cat", "elephant", "waffle"] | t | t | f | f (2 rows)