how extract 'cancellationDate' this:

Question

["{\"RequestedByUser\":false,\"RequestedBySystem\":null,\"RequestedBySellerNotification\":null,\"RequestedByPaymentNotification\":true,\"Reason\":null,\"CancellationDate\":\"2024-01-16T00:40:59.0928615+00:00\"}"]

tryingjson_extract, jsonpath and nothing

Answer 1

We have a JSON string inside a list. If you want to extract values from this JSON string, you can use the json module in Python.

Try like this

import json

# Your input data
data = ["{\"RequestedByUser\":false,\"RequestedBySystem\":null,\"RequestedBySellerNotification\":null,\"RequestedByPaymentNotification\":true,\"Reason\":null,\"CancellationDate\":\"2024-01-16T00:40:59.0928615+00:00\"}"]

# Assuming there is only one element in the list, you can access it using data[0]
json_data = json.loads(data[0])
cancellation_date = json_data["CancellationDate"]
print("CancellationDate:", cancellation_date)

Output:

CancellationDate: 2024-01-16T00:40:59.0928615+00:00

Answer 2

I assume that you get a list of Json strings, from that you want to get the CancellationDate. You can do this in python, by loading the json string in the json module. Like this example:

import json

json_string_list = ["{\"RequestedByUser\":false,\"RequestedBySystem\":null,\"RequestedBySellerNotification\":null,\"RequestedByPaymentNotification\":true,\"Reason\":null,\"CancellationDate\":\"2024-01-16T00:40:59.0928615+00:00\"}"]
new_json_list = []

for json_string in json_string_list:
    try:
        json_dict = json.loads(json_string)
    except JSONDecodeError:
        continue
    new_json_list.append(json_dict)

if new_json_list:
    print(new_json_list[0]["CancellationDate"])

Answer 3

You didn't clarify what exactly it is that you're showing. If that's what's in the database, the outer square brackets [ would mean that your value is a single-element json or jsonb array. A simpler example would be this:

["abcd\",\"efgh"]

The middle quotes are escaped with \, making the whole thing between the brackets one long string with quotes in it. If that's the case, you'll need to check if that's what you intended to save there.

You can still get that element, treat it as a json and ask for your target key in it.

select ((your_json_column->>0)::json)->>'CancellationDate' from your_table;

If you're getting that after fetching the value from the db to your app, it could be your client mapping PostgreSQL's json/jsonb to Python string type and giving you an array of that, as a result list to iterate over. That's mapping a single row resulting from a SQL query to a Python array/list with one element. In that case, instead of pulling the whole thing, you can get your value out of the json right in the db:

select your_json_column->>'CancellationDate' from your_table;

That way you don't have to read, transfer, map the whole thing from db to Python, then take one thing out - you only get what you wanted in the first place. If there are multiple rows, you'll get an array of just the cancellation dates.

Demo at db<>fiddle.