SQL query to read nested JSON object

Question

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.

LIKE is used for text, not for JSON. Did you check the manual? postgresql.org/docs/current/functions-json.html — Frank Heikens
– Frank Heikens, Commented Nov 20, 2022 at 16:08
MySQL and PostgreSQL are two different products. Please keep only one tag for the DBMS. — Barbaros Özhan
– Barbaros Özhan, Commented Nov 20, 2022 at 17:09

nbk · Accepted Answer · 2022-11-20 16:21:27Z

1

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

answered Nov 20, 2022 at 16:21

nbk

49.6k8 gold badges32 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Luuk · Accepted Answer · 2022-11-20 16:25:50Z

1

Extracting the video-url from the metadata:

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

see: DBFIDDLE

answered Nov 20, 2022 at 16:25

Luuk

15.4k5 gold badges28 silver badges44 bronze badges

Collectives™ on Stack Overflow

SQL query to read nested JSON object

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related