SQL query to read nested JSON object-CodePudding

How to read JSON object matching query params? I want to filter data for video urls which only contains query params from metadata column and replace it by the removing the query params.

metadata
{"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"}

select * from content where metadata.video-url ilike %?pubtool%

Expected to return rows which consists of query param in the metadata column for the field video-url.

CodePudding user response：

You need irst to extract the vidourl from the json and compare it to your search pattern.

You need to convert the result to varchar to use like

SELECT
"metadata"
FROM video 
WHERE ("metadata"  #> '{"video-url"}')::varchar like '%?pubtool%'

metadata
{"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"}

SELECT 2

fiddle

CodePudding user response：

Extracting the video-url from the metadata:

SELECT
  metadata->>'video-url' as "video-url"
FROM m;

see: DBFIDDLE