I have a table called Stores which has a key called store_id and a table called Sales which contains a store_id reference and a json field called sales_json.

sales_json looks something like this:

[{'start_date': '2-20-15', 'end_date': '2-21-15', 'start_time': '11:00 AM',
  'end_time': '11:00 PM', 'discount_percentage': 20}, etc]

I have a plpgsql function in which I'm trying to determine if a store is currently having a sale and store a boolean called having_sale in the result table depending on whether this is true or not.

My SELECT statement looks like this:

SELECT Stores.store_id,
       CASE WHEN (SELECT COUNT(*)
                  FROM (SELECT *
                        FROM json_array_elements(sales_json) AS sale
                        WHERE (now()::date BETWEEN (sale->>'start_date')::date AND
                                                   (sale->>'end_date')::date) AND
                              (now()::time BETWEEN (sale->>'start_time')::time AND
                                                   (sale->>'end_time')::time)
                       ) AS current_sales) > 0
                  THEN TRUE
            ELSE FALSE
       END) AS having_sale
FROM Stores INNER JOIN Sales
ON Stores.store_id = Sales.store_id;

The code looks correct to me but I get the following error:

psycopg2.DataError: cannot extract field from a non-object

What am I doing wrong and how do I fix it?

The error was caused by a function I wrote that appends a json array to sales_json. I fixed it and now it looks like this:

CREATE OR REPLACE FUNCTION add_sales (insertion_id smallint, new_sales_json json)
RETURNS void AS $$
BEGIN
    UPDATE Sales
    SET sales_json = array_to_json(ARRAY(SELECT * FROM json_array_elements(sales_json)
                                         UNION ALL SELECT json_array_elements(new_sales_json)))
    WHERE store_id = insertion_id;
END;
$$ LANGUAGE plpgsql;

I was previously missing the json_array_elements() call on new_sales_json, which is why I was getting an array within an array and thus the cannot extract field from a non-object error.

more