2

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?

2

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 ->> 'grade' as "grade",
       t.doc ->> 'endYear' as "endYear", 
       t.doc ->> 'startYear' as "startYear",
       t.doc ->> '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

1

I've figured this out. The problem was in a specific row of data:

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

The null in the end was breaking jsonb_to_record. Once I deleted it, everything works.

I don't know if this could happen thru normal usage, but in my case, I think this was because I wrote the data to the DB from a nodejs script, but accidentally set the destination column to type TEXT instead of type JSONB.

I changed the type to JSONB with the data already in it, so the null must have sneaked in that way.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.