‘cannot extract element from a scalar’ when order by id is DESC

I have a PostgreSQL 9.3.10 database, a table with 15842 records and use Intellij for making queries. I want to query some recent data from JSON column (named ‘data’).

My query is that simple:

select data->'header'
from some_table
order by id desc

The problem is that I get:

[22023] ERROR: cannot extract element from a scalar

when I use desc order. When I use asc the query does not fail and at least first 500 records are received.

For me it looks like some recent record could have invalid JSON. If so, how can I ignore bad record and still perform the query with DESC ordering? Or how to identify broken JSON record?

I also thought that it can be caused by null data but select * from table where data is null does not show any results.

UPD: The type of the ‘data’ column is json. I found that there are rows having json data serialized to string (because of faulty backend). So instead of normal json object: {} the values is a quoted object string "{}". Detected by the following query: select data from some_table where data::text not like '{%'

Answer

The problem was caused by ActiveRecord version update which caused a Ruby object receiving to_json explicitly to be written as a single string which is a valid JSON data too (Postgres calls it: “scalar” in error message). That’s why an error only appeared in query using by id desc (only for new records created after ActiveRecord update). Here is a note on change in ActiveRecord: https://github.com/rails/rails/commit/835246e622dc0431af3cb951db22ef78876006af

So instead of {"header": "value"} it was written as "{"header": "value"}". The database was fixed with the following query:

update some_table 
set data = REPLACE(TRIM(BOTH '"' from data::text), '"', '"')::JSON 
where data::text like '"%';