Postgres: What could cause the error "cannot call populate_composite on a scalar"? I have a column of type JSONB, where each row contains a JSON with an array of objects, eg:

[
    {
        "grade": "4.44/5",
        "endYear": 2011,
        "startYear": 2006,
        "userId": "defg"
    },
    {
        "grade": "9.133/10",
        "endYear": 2010,
        "startYear": 2006,
        "userId": "abcd"
    }
]


I'm attempting to expand those JSONB collections into rows, like:

| grade    | startYear | endYear | userId |
-------------------------------------------
| 4.44/5   |    2006   | 2011    | defg   |
| 9.133/10 |    2006   | 2010    | abcd   |
-------------------------------------------


..using the following query:

WITH arr AS (SELECT jsonb_array_elements(jsonbrecords) AS jsons
             FROM "table-with-jsonb"),
    lines AS (
      SELECT x.*
      FROM arr, jsonb_to_record(jsons) AS x(
                "field1" VARCHAR
                 )
  ) SELECT *
    FROM lines


I'm using Datagrip, which paginates results, and the query finishes fine for the first 500 rows.

However, when I try to load the last page of results, I get this error: 

[22023] ERROR: cannot call populate_composite on a scalar

Googling this error shows almost no useful results (a first for me) except for the Postgres source code.

I don't speak C, but since the jsonb_array_elements CTE evaluates fine on its own, I am assuming the problem is that some row(s) have a scalar value in jsonbrecords column instead of a proper JSONB.

To ensure I had only JSONB values, I stripped the rows that didn't contain '{' with this query:

UPDATE  "table-with-jsonb" SET jsonbrecords = NULL
WHERE jsonbrecords :: TEXT !~ '{'


This deleted about a dozen rows, but the error remains.

I've also tried to find the problem by inspecting the raw JSONB rows in the table, but have found nothing. 

Am I correct in thinking the error means a row has a non-JSONB value in it? If so, how can I fix? If not, how can I debug?
Unrelated, but: you can simplify your query to:

SELECT x.*
from the_table, 
     jsonb_array_elements(jsonbrecords) AS t(doc),
     jsonb_to_record(t.doc) as x ("grade" text, "userId" text, "endYear" int, "startYear" int);


Now for the actual question. The error can be avoided by not using jsonb_to_record and accessing each key individually instead:

SELECT t.doc -&gt;&gt; 'grade' as "grade",
       t.doc -&gt;&gt; 'endYear' as "endYear", 
       t.doc -&gt;&gt; 'startYear' as "startYear",
       t.doc -&gt;&gt; 'userId' as "userId"
from the_table, 
     jsonb_array_elements(jsonbrecords) AS t(doc);


You don't really lose flexibility as you need to specify the column list with jsonb_to_record() as well. 

Online example: http://rextester.com/VVGJ34083