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.