Postgresql 字符串截取

在一次数据迁移中，遇到的一个场景。需要从一串 JSON 中提取其中一段字符串。在 Bing 上搜索发现很少在 Postgresql 中用到字符串截取的。下面是解决的方法，不一定好，但恰好可以解决问题。

JSON 字符串

'{"item":{"id":"c0540d7e-bedb-4521-b2b1-401d099575d1","name":"这时一张图片.jpg","path":"5cbf3cff-4be0-4f82-b21a-34ccbb93e830/15a8164e-6244-4369-96bd-3fa5f6a24ff9/823b93f69867ad18501057e4312793dc.jpg","createdUserId":"5cbf3cff-4be0-4f81-b21a-34ccbb93e830","hash":"823b93f69867ad18501057e43e2793dc","createdUserName":"Admin","type":".jpg","size":47505408,"createdAt":"2019-05-25T11:54:33.5207074+08:00","url":"http://221.178.13.191:9021/5cbf3cff-4be0-4f81-b21a-34ccbb93e830/15a8164e-6244-4369-96bd-4fa5f6a24ff9/823b93f69867ad18501057e43e2793dc.jpg","completed":false,"tag":null,"tags":["BIM_MANAGER","500103","MUNICIPAL","CONSTRUCTION_APPLY_PHASE"]},"success":true}'

原始信息如上，现在需要将粗体部分截取出来。用到时方法是 regexp_split_to_table，该方法可以将字符串分割成行。

第一次执行（其中第二个参数的 E 是 Postgresql 正则的语法）

SELECT regexp_split_to_table(JSON字符串, E',"path":"')

图1

可以看到第二行中已经出现了我们需要的结果了。但是还不能直接使用。于是。。。再截一次。

第二次执行（得到最终结果）

SELECT regexp_split_to_table
(
	regexp_split_to_table
	(
		JSON字符串, E',"path":"'
	), 
	E'.jpg",'
) 
|| '.jpg' 
LIMIT 1 OFFSET 1;

图2

说明：

1.第一层 regexp_split_to_table 得到两条数据如图1

2.第二层 regexp_split_to_table 从第一层的第二条结果中继续截取，得到数据如图2

3.图2中第二条数据就离最终结果非常接近了，所以再给字符串后面加上.jpg

4.使用 LIMIT 1 OFFSET 1 语句获取第二条数据，就是最终的结果数据如图3

图3