json - 查询从 Postgres 中的 Json 中提取

Question

我的 postgres 数据库中有一个 json 对象，如下所示

{"Actor":[{"personName":"Shashi Kapoor","characterName":"Prem"},{"personName":"Sharmila Tagore","characterName":"Preeti"}, {"personName":"Shatrughan Sinha","characterName":"Dr. Amar"]}

已编辑(来自编辑:保留原件，因为它是无效的 json，在我的编辑中我修复了它)

{  
   "Actor":[  
      {  
         "personName":"Shashi Kapoor",
         "characterName":"Prem"
      },
      {  
         "personName":"Sharmila Tagore",
         "characterName":"Preeti"
      },
      {  
         "personName":"Shatrughan Sinha",
         "characterName":"Dr. Amar"
      }
   ]
}

列的名称是 xyz，我有一个相应的 content_id。

我需要检索具有 Actor & personName = Sharmila Tagore 的 content_ids。

我尝试了很多查询，其中这两个查询很可能得到但我仍然没有得到。

SELECT content_id 
  FROM content_table 
 WHERE cast_and_crew #>> '{Actor,personName}' = '"C. R. Simha"'

.

SELECT cast_and_crew ->> 'content_id' AS content_id 
  FROM content_table 
 WHERE cast_and_crew ->> 'Actor' -> 'personName' = 'C. R. Simha'

Answer 1

你应该使用 jsonb_array_elements()在嵌套的 jsonb 数组中搜索:

select content_id, value 
from content_table, 
lateral jsonb_array_elements(cast_and_crew->'Actor');

 content_id |                              value                              
------------+-----------------------------------------------------------------
          1 | {"personName": "Shashi Kapoor", "characterName": "Prem"}
          1 | {"personName": "Sharmila Tagore", "characterName": "Preeti"}
          1 | {"personName": "Shatrughan Sinha", "characterName": "Dr. Amar"}
(3 rows)    

列 value 是 jsonb 类型，因此您可以为其使用 ->> 运算符:

select content_id, value 
from content_table, 
lateral jsonb_array_elements(cast_and_crew->'Actor')
where value->>'personName' = 'Sharmila Tagore';

 content_id |                            value                             
------------+--------------------------------------------------------------
          1 | {"personName": "Sharmila Tagore", "characterName": "Preeti"}
(1 row) 

请注意，如果您使用的是 json(不是 jsonb)，当然使用 json_array_elements()。