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.