postgresql - 我如何选择“职业”？

我有以下资料：

SELECT *
FROM (
   SELECT '{"people": [{"name": "Bob", "occupation": "janitor"}, {"name": "Susan", "occupation": "CEO"}]}'::jsonb as data
) as b
WHERE data->'people' @> '[{"name":"Bob"}]'::jsonb;

我正在筛选对象{“name”：“Bob”，“ocposition”：“janitor”}
我该如何归还鲍勃的职业（“看门人”）？

SELECT data->'people'->>'occupation'
FROM (
   SELECT '{"people": [{"name": "Bob", "occupation": "janitor"}, {"name": "Susan", "occupation": "CEO"}]}'::jsonb as data
) as b
WHERE data->'people' @> '[{"name":"Bob"}]'::jsonb;

回报

?column?
--------
NULL

寻找：

occupation
----------
janitor

最佳答案

如果您不关心jsonb所在行上的任何其他内容，则可以从jsonb中取出所有元素，然后将它们作为单独的元素进行选择

SELECT data->>'occupation' as occupation
FROM (
  SELECT jsonb_array_elements(
    '{"people":
       [
         {"name": "Bob", "occupation": "janitor"},
         {"name": "Susan", "occupation": "CEO"}
       ]
     }'::jsonb->'people') as data) as b
WHERE data @> '{"name":"Bob"}';

结果
职业
-----------
看门人
（1行）

关于postgresql - 我如何选择“职业”？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/30696915/