PostgreSQL's JSONB_BUILD_OBJECT function does not seem to work

[marcustut]

Version

1.10.0

What happened?

I was trying to generate from the following SQL query which uses the function JSONB_BUILD_OBJECT to form JSONB from columns. It was working when I run it in PSQL. but when I do sqlc generate it throws the following error. I'm guessing is it because SQLC is unable to detect the type for nested JSONB fields?

Relevant log output

# package db
db/query/listings.sql:13:4: function jsonb_build_object(unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown) does not exist

Database schema

CREATE TABLE public.listings (
    listing_id uuid NOT NULL,
    brand_id uuid NOT NULL,
    category_id uuid NOT NULL,
    active boolean NOT NULL,
    title text NOT NULL,
    image text NOT NULL,
    description text NOT NULL,
    rating double precision NOT NULL,
    created_at timestamp without time zone DEFAULT now() NOT NULL,
    updated_at timestamp without time zone DEFAULT now() NOT NULL
);

CREATE TABLE public.categories (
    category_id uuid NOT NULL,
    name character varying(100) NOT NULL
);

CREATE TABLE public.tags (
    tag_id uuid NOT NULL,
    name character varying(20) NOT NULL,
    description text NOT NULL,
    created_at timestamp without time zone DEFAULT now() NOT NULL,
    updated_at timestamp without time zone DEFAULT now() NOT NULL
);

CREATE TABLE public.tags_listings (
    tag_id uuid NOT NULL,
    listing_id uuid NOT NULL
);

SQL queries

-- name: GetListingByListingId :one
SELECT
    L.listing_id,
    L.active AS "listing_active",
    L.title AS "listing_title",
    L.image AS "listing_image",
    L.description AS "listing_description",
    L.rating AS "listing_rating",
    L.created_at AS "listing_created_at",
    L.updated_at As "listing_updated_at",
	(
		SELECT
			JSONB_BUILD_OBJECT(
                'brand_id', B.brand_id, 
                'rating', B.rating, 
                'name', B.name, 
                'address', B.address, 
                'profile_image', B.profile_image, 
                'cover_image', B.cover_image, 
                'created_at', B.created_at, 
                'updated_at', B.updated_at, 
                'email', B.email, 
                'phone', B.phone, 
                'website', B.website
            )
		FROM
			public.brands B,
			public.listings L
		WHERE L.listing_id = $1 AND
			B.brand_id = L.brand_id
	) AS "brand",
	(
		SELECT
			JSONB_BUILD_OBJECT(
                'category_id', C.category_id, 
                'name', C.name
            )
		FROM
			public.categories C,
			public.listings L
		WHERE 
			L.listing_id = $1 AND
			C.category_id = L.category_id
	) AS "category",
	(
		SELECT 
			JSONB_AGG(
                JSONB_BUILD_OBJECT(
                    'tag_id', T.tag_id, 
                    'name', T.name, 
                    'description', T.description, 
                    'created_at', T.created_at, 
                    'updated_at', T.updated_at
                )
            )
		FROM 
			public.tags T,
			public.tags_listings TL,
			public.listings L
		WHERE
			L.listing_id = $1 AND
			T.tag_id = TL.tag_id AND
			TL.listing_id = L.listing_id
	) AS "tags"
FROM
    public.listings L
WHERE
    L.listing_id = $1
LIMIT 1;

Configuration

version: '1'
packages:
  - name: 'db'
    path: 'db'
    queries: './db/query/'
    schema: './db/schema.sql'
    engine: 'postgresql'
    sql_package: 'pgx/v4'
    emit_json_tags: true
    emit_prepared_queries: true
    emit_interface: false
    emit_exact_table_names: false
    emit_empty_slices: false
    json_tags_case_style: 'snake'

Playground URL

https://play.sqlc.dev/p/dfba09f5f00a1a12625473f33782157047d713b8a3acd64695ee32c3bcd69ea7

What operating system are you using?

Linux

What database engines are you using?

PostgreSQL

What type of code are you generating?

Go

[kyleconroy]

I'm guessing is it because SQLC is unable to detect the type for nested JSONB fields?

Looks like the function signature for jsonb_build_object was incorrect. jsonb_build_array also had the same problem. Both have now been fixed.

[marcustut]

Yup, just tested on the latest commit, it works perfectly fine now. Thanks for the fix ;)