表rabbits数据如下:
id | info |
1 | {"name":"Henry", "food":["lettuce","carrots"]} |
2 | {"name":"Herald","food":["carrots","zucchini"]} |
3 | {"name":"Helen", "food":["lettuce","cheese"]} |
检查food数组是否包含某字符串:
SELECT info->>'name' FROM rabbits WHERE info->'food' @> '"carrots"'
注意引号,PGSQL里JSONB中的元素都是字符串,如:boolean: 'true', string: '"hello"', integer: '123',上面的SQL应该是这样的:
SELECT info->>'name' FROM rabbits WHERE (info->'food')::JSONB @> '"carrots"'::JSONB
另一种方式:存在操作符,检查一个字符串是否出现在JSONB数据顶层对象中
SELECT info->>'name' FROM rabbits WHERE (info->'food')::JSONB ? 'carrots'
另另一种方式:直接当做字符串来检查
SELECT info->>'name' FROM rabbits WHERE info->>'food' LIKE '%"carrots"%'
文章评论