Question 113684 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – SQL query to read nested JSON object

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.

Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    Extracting the video-url from the metadata:

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

    see: DBFIDDLE

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search