others - sql - 在postgres json字段数组中,如何选择与值匹配的记录?

我正在尝试返回与json字段中等于特定值的数组元素匹配的记录。

例子data


{"name":"Bob","scores": [64, 66]}


{"name":"Sally","scores": [66, 65]}


{"name":"Kurt","scores": [69, 71, 72, 67, 68]}


{"name":"Libby","scores": [72, 73, 74, 75]}


{"name":"Frank","scores": [80, 81, 82, 83]}



尝试运行此查询:


SELECT data


FROM tests.results


where (data->>'scores') @> '[72]';



我希望结果中有两行:


{"name":"Kurt","scores": [69, 71, 72, 67, 68]}


{"name":"Libby","scores": [72, 73, 74, 75]}



但我得到了:


SQL Error [42883]: ERROR: operator does not exist: text @> integer


 Hint: No operator matches the given name and argument type(s). 


 You might need to add explicit type casts.



我目前正在使用Postgres 10,感谢你的帮忙。

时间:

你需要使用->而不是->> ,前者返回一个json对象,而后者返回文本; 同时,@>操作符在json对象上操作,而不是在文本上操作。


SELECT data


FROM tests.results


where (data->'scores')::jsonb @> '[72]';



DB Fiddle上的演示


WITH results AS (


 SELECT '{"name":"Bob","scores": [64, 66]}'::json mydata


 UNION ALL SELECT '{"name":"Sally","scores": [66, 65]}'::json


 UNION ALL SELECT '{"name":"Kurt","scores": [69, 71, 72, 67, 68]}'::json


 UNION ALL SELECT '{"name":"Libby","scores": [72, 73, 74, 75]}'::json


 UNION ALL SELECT '{"name":"Frank","scores": [80, 81, 82, 83]}'::json


)


SELECT mydata::text


FROM results


where (mydata->'scores')::jsonb @> '[72]';



| mydata |


| ------------------------------------------------ |


| {"name":"Kurt","scores": [69, 71, 72, 67, 68]} |


| {"name":"Libby","scores": [72, 73, 74, 75]} |



...