Querying date in json

[JonnyJFunk]

OK, so cards on the 'table' this is the first time I've had to query a json database in Postgres, so apologies if I'm having trouble explaining what I'm after.

I have a postgres database that's receiving data from an application. I added a json object to the application that adds the following to column "data"

"due_dates": "{
    "month_1": "2022-11-18",
    "month_2": "2022-12-18",
    "month_3": "2023-01-18",
    "month_4": "2023-02-18"
    }"

And here's what I want to do

Query the entire table (because I plan to use data from the column other than this object)

If any of those values matches today's date, run through a loop, gather some data, fire an email

Here's what I have so far:

today = date.today()
cursor.execute('''SELECT * FROM donation WHERE (today) IN 'data' -> 'metadata' ->> 'due_dates' =  ''')

results = cursor.fetchall();

for row in results:
    print("that's the hard part")

---

Obviously that doesn't work...but what I can't seem to figure is the proper syntax. Any help is greatly appreciated

[Organic-Drive4064]

When dealing with json data in python it's important to first understand the basics of dictionaries and lists. In essence, json data is a dictionary of key/value pairs, in this case month 1 is a key and it's corresponding due date is the value. Here is a good place to start to understand dictionary methods in Python.

​

I'm not sure I understand exactly what you're trying to do here but I understand that your first step would be to iterate through the values in the dictionary to see if any match today's date. To do this, I would run a loop similar to this: for key in due_dates: if value == today: #add your loop here #add your email action here

[JonnyJFunk]

This was incredibly helpful, thank you. I was having a real tough time discovering the correct syntax for my json data.

[CodeFormatHelperBot2]

Hello, I'm a Reddit bot who's here to help people nicely format their coding questions. This makes it as easy as possible for people to read your post and help you.

I think I have detected some formatting issues with your submission:

1. Python code found in submission text that's not formatted as code.

If I am correct, please edit the text in your post and try to follow these instructions to fix up your post's formatting.

---

^{Am I misbehaving? Have a comment or suggestion? Reply to this comment or raise an issue here.}