Link: https://adventofsql.com/challenges/1

Solution: https://github.com/kiran-4444/advent-of-sql-2024/blob/main/day-1.sql

I really like the problem. I actually thought that I’d get bored with this as I’m not that interested in writing raw SQL queries. But I was wrong. I thoroughly enjoyed my time working on the first problem.

I had to go through PostgreSQL’s JSON documentation to see how to get values from a JSON field.

Operator Right Operand Type Description Example
-> int Get JSON array element '[1,2,3]'::json->2
-> text Get JSON object field '{"a":1,"b":2}'::json->'b'
->> int Get JSON array element as text '[1,2,3]'::json->>2
->> text Get JSON object field as text '{"a":1,"b":2}'::json->>'b'
#> array of text Get JSON object at specified path '{"a":[1,2,3],"b":[4,5,6]}'::json#>'{a,2}'
#>> array of text Get JSON object at specified path as text '{"a":[1,2,3],"b":[4,5,6]}'::json#>>'{a,2}'

Then I went to see the JOIN syntax from the W3 Schools website.

Finally, I saw the CASE statement syntax.

That’s it! It’s all about using the right operators for accessing JSON fields, making the right JOINS and constructing simple CASE statements.