json操作符

入门演示

--- 使用::符号把TEXT类型的字段转为json类型
SELECT material_test_require_attach::json->>'name' FROM adm.adm_mbr_cus_brand_anly_fill_nd amcbafn;

-- 作为查询显示，选择json数据的指定字段值
SELECT data_format->>'Title' AS 商品标题, data_format->'Url' AS 链接地址
FROM craw.ods_cwr_end_url_request_nd WHERE id = 1653685278931423232;

--- 作为where条件，选择json数据的指定字段值
SELECT * FROM item_detail WHERE data_raw->>'ASIN' = 'B08F24JYQ7';

取出json对象中的数组

select
"start" 起始标记,
json_array_elements(data_format->'SearchResults'->'Results')->>'companyName' as 公司名称,
json_array_elements(data_format->'SearchResults'->'Results')->>'employeesTotal' as 员工总数
from craw.ods_cwr_end_url_request_nd
where site_id = 1654385291466641408 and "group" = 1 and step = 1 ORDER by start asc limit 50;

SELECT 
(json_array_elements((data_format#>>'{SearchResults, Results}')::json)::json)#>>'{companyName}' AS col1
from craw.ods_cwr_end_url_request_nd where id='1654396437884047360';

更新json字段的属性值

-- 属性值为字符串
SELECT jsonb_set('{"x": 1}', '{x}', '"x"');
-- 属性值为整数
SELECT jsonb_set('{"x": 1}', '{y}', '2');

-- 属性值为整数，从每一行的B字段的json属性值，去更新A字段的json属性值
update craw.ods_cwr_end_url_request_nd
set data_format = jsonb_set(data_format::jsonb, '{TotalReviews}', cast(cast(data_raw as json)->'props'->'pageProps'->'productData'->'reviews'->>'total' as jsonb))
where site_id = 1663487130887786496 and step=0 and cast(data_raw as json)->'props'->'pageProps'->'productData'->>'reviews' <>''

注: 更改 json的 TotalReviews 属性值时，如果赋值强转为 integer 类型会报错误

function jsonb_set(jsonb, unknown, integer) does not exist:

SQL 错误 [42883]: ERROR: function jsonb_set(jsonb, unknown, integer) does not exist
  Hint: No function matches the given name and argument types. You might need to add explicit type casts.
  Position: 59

更改Postgres中JSONB列中的值类型 https://cloud.tencent.com/developer/ask/sof/390549/answer/638486
PostgreSQL jsonb_set() 函数 https://www.sjkjc.com/postgresql-ref/jsonb_set/
9.16. JSON Functions and Operators https://www.postgresql.org/docs/current/functions-json.html

优化查询

SELECT
  id AS slice_id,
  json_array_elements(facebook_results -> 'table' -> 'matches') -> 'table' -> 'size'       AS match_size,
  json_array_elements(facebook_results -> 'table' -> 'matches') -> 'table' -> 'score'      AS match_score,
  json_array_elements(facebook_results -> 'table' -> 'matches') -> 'table' -> 'width'      AS match_width,
  json_array_elements(facebook_results -> 'table' -> 'matches') -> 'table' -> 'format'     AS match_format,
  json_array_elements(facebook_results -> 'table' -> 'matches') -> 'table' -> 'domain'     AS match_domain,
  json_array_elements(json_array_elements(facebook_results -> 'table' -> 'matches') -> 'table' -> 'nodes') -> 'table' -> 'crawl_date' AS node_crawl_date,
  json_array_elements(json_array_elements(facebook_results -> 'table' -> 'matches') -> 'table' -> 'nodes') -> 'table' -> 'url'        AS node_url
FROM slices
WHERE id = 169

优化后如下所示:
在线代码

SELECT
  id AS slice_id,
  s.t -> 'size'       AS match_size,
  s.t -> 'score'      AS match_score,
  s.t -> 'width'      AS match_width,
  s.t -> 'format'     AS match_format,
  s.t -> 'domain'     AS match_domain,
  s.t2-> 'crawl_date' AS node_crawl_date,
  s.t2-> 'url'        AS node_url
FROM slices
,LATERAL (
SELECT json_array_elements(facebook_results -> 'table' -> 'matches') -> 'table',
json_array_elements(json_array_elements(facebook_results -> 'table' -> 'matches') 
           -> 'table' -> 'nodes') -> 'table') s(t,t2)
WHERE id = 169;

PostgreSQL中JSON函数和操作符 https://blog.csdn.net/lingyiwin/article/details/107100227
Postgresql中JSON数据构造与操作符实例 https://blog.csdn.net/jackgo73/article/details/127889035
PostgreSQL查询JSON中的数组 https://blog.csdn.net/qq_41757801/article/details/110535245
postgres:如何优化使用多个json_array_elements()调用的查询 http://ask.sov5.cn/q/B05FfILMfq