I have a JSON document such as:

{ "type" : "episode", "AssetId" : "urn:episode:ABCDEFG", "_id" : { "$oid" : "12345678" } }

Here is the table definition:

CREATE TABLE "v22"."assets_exp" (
    "_id" UUID NOT NULL,
 "references.descendants" JSON,

I would like to select type, AssetId in addition to "$oid" as "_id".

I tried json_populate_record:

CREATE TYPE descendant_t AS (
    "type" text,
    "AssetId" text

select r.* 
from  "v22"."assets_exp", 
lateral json_populate_record(null::descendant_t, "references.descendants") r;

...but I get an error message, either that

function json_populate_record doesn't exist


ERROR: cannot call populate_composite on an array.

Any ideas?

The only function that works on this data is json_array_elements_text.

I am using PostgreSQL 11 and the data is imported from Mongo hence the naming of the columns.

  • 1
    Your version of Postgres is obviously relevant. – Erwin Brandstetter May 16 at 23:42
  • 1
    function json_populate_record doesn't exist is not a Postgres error message, which would look like ERROR: function json_populate_record(myrowtype2, text) does not exist. Please always show verbatim error messages. And is there an actual column named assets_exp."references.descendants"? (Really?!) Arriving at my mantra: please disclose your actual table definition (CREATE TABLE script). – Erwin Brandstetter May 16 at 23:52

Your Answer

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

Browse other questions tagged or ask your own question.