15.postgresql--jsonb 数组进行打平，过滤

用jsonb_array_elements函数先展开数组，再用jsonb_each函数遍历元素中的键值对

例如：

SELECT *
FROM data_table,
LATERAL jsonb_array_elements(json_column) WITH ORDINALITY as elem(element, idx)
JOIN LATERAL jsonb_each(elem.element) as kv(key, value) ON TRUE
WHERE kv.key = 'key_to_find' AND kv.value > 10;

name |rule_types |
------±---------------------------------------------------------------+
TIKTOK|[{“key”: “A”, “name”: “NAME_A”}, {“key”: “B”, “name”: “NAME_B”}]|
SHANG |[{“key”: “A”, “name”: “NAME_A”}, {“key”: “B”, “name”: “NAME_B”}]|
OTHER |[{“key”: “C”, “name”: “NAME_C”}, {“key”: “D”, “name”: “NAME_D”}]|

SELECT *
FROM ecomm.ywl_test ,
LATERAL jsonb_array_elements(rule_types) WITH ORDINALITY as elem(element, idx)
JOIN LATERAL jsonb_each(elem.element) as kv(key, value) ON TRUE

在这里插入图片描述
过滤

  SELECT *
FROM ecomm.ywl_test ,
LATERAL jsonb_array_elements(rule_types) WITH ORDINALITY as elem(element, idx)
JOIN LATERAL jsonb_each(elem.element) as kv(key, value) ON TRUE
WHERE kv.key = 'key' AND kv.value ='"A"';

原文地址：https://blog.csdn.net/qq_24223159/article/details/143978932

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！