我如何选择“职业”？

Question

我有以下几点：

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

我正在过滤对象{“名称”："Bob"，“职业”：“门卫”}‘

我该如何回鲍勃的职业(“看门人”)？

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

Answer 1

如果您不关心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"}';

结果

职业janitor (1行)

Answer 2

您的“人员”元素是一个数组。您可以使用jsonb_array_elements函数获取数组的元素。之后，您可以在person->>'name'上进行筛选。

SELECT  person->>'occupation' as occupation
FROM    (
        SELECT  person.value as person
        FROM    (
                SELECT     
                  '{"people": 
                     [
                       {"name": "Bob", "occupation": "janitor"}, 
                       {"name": "Susan", "occupation": "CEO"}
                     ]
                   }'::jsonb as data
                ) a
        CROSS JOIN
                jsonb_array_elements(data->'people') as person
        ) b
WHERE   person->>'name' = 'Bob';

注意，->>返回文本，而->返回jsonb。