提取PostgreSQL嵌套JSON中的网站域名与IPv4地址信息

针对你需要从嵌套JSON中提取网站域名和对应IPv4地址的需求，可以通过PostgreSQL的JSONB函数逐步拆解数据，以下是可行的查询语句及说明：

基础查询语句（适配顶层含多个testSettings键的结构） #

SELECT 
  js_website.key AS website, 
  ip_addr->>'ipv4Addr' AS ipv4Addr
FROM 
  public."MyTable",
  -- 展开顶层JSON的键值对，筛选出所有testSettings对象
  jsonb_each(exampleColumn::jsonb) js_test,
  -- 展开每个testSettings下的网站域名，过滤以www开头的条目
  jsonb_each(js_test.value) js_website,
  -- 展开每个网站对应的IpAddress数组
  jsonb_array_elements(js_website.value->'IpAddress') ip_addr
WHERE 
  js_test.key = 'testSettings'
  AND js_website.key LIKE 'www.%';

适配testSettings为数组的结构 #

如果你的exampleColumn本身是包含多个testSettings对象的数组（比如[{"testSettings": {...}}, {"testSettings": {...}}]），使用以下查询：

SELECT 
  js_website.key AS website, 
  ip_addr->>'ipv4Addr' AS ipv4Addr
FROM 
  public."MyTable",
  -- 展开exampleColumn数组中的每个元素
  jsonb_array_elements(exampleColumn::jsonb) elem,
  -- 提取每个元素内的testSettings下的网站域名
  jsonb_each(elem->'testSettings') js_website,
  -- 展开IpAddress数组
  jsonb_array_elements(js_website.value->'IpAddress') ip_addr
WHERE 
  js_website.key LIKE 'www.%';

处理NULL值的增强版本 #

如果存在没有testSettings、无符合条件的网站或IpAddress为空的情况，使用左连接避免丢失数据：

SELECT 
  js_website.key AS website, 
  ip_addr->>'ipv4Addr' AS ipv4Addr
FROM 
  public."MyTable"
LEFT JOIN jsonb_each(exampleColumn::jsonb) js_test 
  ON js_test.key = 'testSettings'
LEFT JOIN jsonb_each(js_test.value) js_website 
  ON js_website.key LIKE 'www.%'
LEFT JOIN jsonb_array_elements(COALESCE(js_website.value->'IpAddress', '[]'::jsonb)) ip_addr 
  ON true;

关键函数说明 #

• jsonb_each()：将JSON对象的键值对拆分为多行记录，方便逐个处理嵌套的键。
• jsonb_array_elements()：将JSON数组拆分为多行，实现数组元素的扁平化。
• ->>：从JSON对象中提取指定键的字符串值，确保ipv4Addr以文本形式返回。

内容的提问来源于stack exchange，提问作者OmerFaruk