Cannot extract field from a non-object, Postgres json array

Question

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?

It is possible that some of the elements within one or more of the sales_json arrays are not actually objects? Ie. could you have any null values in there? — harmic
– harmic, Commented Feb 21, 2015 at 4:23
Hi, I'm testing using the sales_json value from above, and it does not contain any null values. — Teboto
– Teboto, Commented Feb 21, 2015 at 4:31
Well, there is an extra bracket in your query above but other than that it works fine for me using the test data you have described above - see sqlfiddle.com/#!15/59fd7/2 - so I think it has something to do with your input data. The only way I could get that error message was by inserting an empty array into one of the sales_json entries, eg: sqlfiddle.com/#!15/e70fa/1 — harmic
– harmic, Commented Feb 21, 2015 at 5:19
That's strange, I'm using the same exact input as the one that works for you, but still no dice :\ — Teboto
– Teboto, Commented Feb 21, 2015 at 6:06
Your sqlfiddle code works for me but my code that uses similar but more complex tables and through psycopg doesn't work for some reason. It may have to do with the typecasting on the json input as it gets called into my function from python. — Teboto
– Teboto, Commented Feb 21, 2015 at 7:11

Teboto · Accepted Answer · 2015-02-21 11:41:06Z

3

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.

answered Feb 21, 2015 at 11:41

Teboto

1,3152 gold badges13 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 11:46:52Z

0

Besides the mix-up of element and array input that you sorted out yourself, you can also largely simplify your SELECT statement:

SELECT store_id
     , EXISTS (
          SELECT 1
          FROM   json_array_elements(s.sales_json) sale
          WHERE  now() BETWEEN (sale->>'start_date')::date + (sale->>'start_time')::time
                       AND     (sale->>'end_date')::date  + (sale->>'end_time')::time
          ) AS having_sale
FROM   Stores st
JOIN   Sales  s USING (store_id);

Shorter and faster.
Adding time and date results in a timestamp [without time zone]. It might be a good idea to store timestamp data instead of date and time to begin with.

Except, if your times are meant as daily schedules, not as absolute bounds in combination with the dates. Then you must keep your original expressions.

Finally, BETWEEN includes both bounds, while you would typically include the lower bound and exclude the upper:

WHERE  now() >= (sale->>'start_date')::date + (sale->>'start_time')::time
AND    now() <  (sale->>'end_date')::date   + (sale->>'end_time')::time

And be aware that your date and time are interpreted according to the current setting of the time zone.

edited May 23, 2017 at 11:46

CommunityBot

11 silver badge

answered Feb 21, 2015 at 12:33

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

3 Comments

Teboto Over a year ago

Given my SELECT statement and sales_json from my original post, it turns out that I get false as the result when today is 2-20-15 and the time is 11:01 PM, when I should actually be getting true. Though your solution doesn't address the original problem which I figured out myself, it does fix this issue. The optimization is just as valuable. Thanks!

Teboto Over a year ago

Note that there's an extra parenthesis here: (sale->>'end_date')::date)

Teboto Over a year ago

and here: ) AS having_sale

Collectives™ on Stack Overflow

Cannot extract field from a non-object, Postgres json array

2 Answers 2

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related