{"video-url"; "xyz.com/video/xy4jnj?pubtool=oembed","provider":"some-video","video-id":"x8cse6q"} {"video-url":"xyz.com/video/x8cse6q?pubtool=oembed","provider":"some-video","video-id":"x8cse6q"}
如何读取JSON对象匹配查询参数?我想过滤视频url的数据,它只包含元数据列的查询参数,并通过删除查询参数来替换它。
元数据 | {"video-url"xyz.com/video/xy4jnj ? pubtool = oembed","provider":"some-video","video-id":"x8cse6q"} |
---|
{"video-url"xyz.com/video/x8cse6q ? pubtool = oembed","provider":"some-video","video-id": x8cse6q} |
{"video-url"xyz.com/video/x8cse6q","provider":"some-video","video-id":"x8cse6q"} |
您需要首先从json中提取视频,并将其与您的搜索模式进行比较。
您需要将结果转换为varchar以便像
那样使用SELECT
"metadata"
FROM video
WHERE ("metadata" #> '{"video-url"}')::varchar like '%?pubtool%'
从元数据中提取video-url
:
SELECT
metadata->>'video-url' as "video-url"
FROM m;
看到:DBFIDDLE