PostgreSQL UUID query issue

Question

I have the following in a TS script that I'm running with the npm tsx package (slicing to 1 item to make it shorter, but same error when using full array):

console.log(psql(`SELECT c.id lead_uuid, part.id storefront_id FROM conversations c CROSS JOIN LATERAL (SELECT parts->>'id' id FROM jsonb_path_query(c.data,'$.participants.*') parts WHERE parts->'tags'@>'["booked"]') part WHERE EXISTS (SELECT 1 FROM jsonb_each(data -> 'participants') AS participants_item WHERE participants_item.value -> 'tags' IS NOT NULL AND jsonb_typeof(participants_item.value -> 'tags') = 'array' AND participants_item.value -> 'tags' @> '["booked"]') AND c.id IN ('${conversationIds.slice(0,1).join("','")}');`));

return await psql`SELECT c.id lead_uuid, part.id storefront_id FROM conversations c CROSS JOIN LATERAL (SELECT parts->>'id' id FROM jsonb_path_query(c.data,'$.participants.*') parts WHERE parts->'tags'@>'["booked"]') part WHERE EXISTS (SELECT 1 FROM jsonb_each(data -> 'participants') AS participants_item WHERE participants_item.value -> 'tags' IS NOT NULL AND jsonb_typeof(participants_item.value -> 'tags') = 'array' AND participants_item.value -> 'tags' @> '["booked"]') AND c.id IN ('${conversationIds.slice(0,1).join("','")}');`;

The console log is this:

Identifier {
  value: `"SELECT c"."id lead_uuid, part"."id storefront_id FROM conversations c CROSS JOIN LATERAL (SELECT parts->>'id' id FROM jsonb_path_query(c"."data,'$"."participants"."*') parts WHERE parts->'tags'@>'[""booked""]') part WHERE EXISTS (SELECT 1 FROM jsonb_each(data -> 'participants') AS participants_item WHERE participants_item"."value -> 'tags' IS NOT NULL AND jsonb_typeof(participants_item"."value -> 'tags') = 'array' AND participants_item"."value -> 'tags' @> '[""booked""]') AND c"."id IN ('91174f9d-b65e-440a-ad3b-7ec18b26b7db');"`
}

But I'm getting this error on the return line:

Fatal error PostgresError: invalid input syntax for type uuid: "$1"

New to PostgreSQL in NodeJS so I'm hoping it's an obvious error that can be pointed out.

return await psql SELECT ... AND c.id IN ('${conversationIds.slice(0,1).join("','")}'); interpolates the value directly into the SQL string, which defeats the purpose of parameterized queries and causes the psql tag to treat the entire string as a single parameter ($1). You need to pass the values as parameters. You should tag psql\... is a tagged template literal that can parse the SQL properly — Ranadip Dutta
– Ranadip Dutta, Commented Nov 3 at 15:35
@RanadipDutta I'm sorry, I am not understanding. Could you provide an example of how to rewrite my query? — mrivera
– mrivera, Commented Nov 3 at 19:30

Ranadip Dutta · Accepted Answer · 2025-11-04 08:15:09Z

0

With respect to the comments, updating the answer with a sample:

const ids = conversationIds.slice(0, 1);

return await psql`
SELECT 
c.id AS lead_uuid, 
part.id AS storefront_id 
FROM conversations c 
CROSS JOIN LATERAL (
SELECT parts->>'id' AS id 
FROM jsonb_path_query(c.data, '$.participants.*') parts 
WHERE parts->'tags' @> '["booked"]'
) part 
WHERE EXISTS (
SELECT 1 
FROM jsonb_each(data -> 'participants') AS participants_item 
WHERE participants_item.value->'tags' IS NOT NULL 
AND jsonb_typeof(participants_item.value->'tags') = 'array' 
AND participants_item.value->'tags' @> '["booked"]'
) 
AND c.id IN (${psql.join(ids, psql.sql`,`)});

Note: I am using slonik library. This avoids the $1 error because each UUID is treated as a separate parameter of type uuid.

Hope it gives you a fair idea on the next steps.

answered Nov 4 at 8:15

Ranadip Dutta

9,5493 gold badges33 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL UUID query issue

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related