Checking presence of specific JSON data/path with Prisma

Question

I have a jsonb field in postgres, where I want prisma to check if it has a certain path with underlaying objects.

Field: locations

Data example #1:
{"hotels":{"id":984}}

Data example #2:
{"restaurants":{"id":293}}

I would like to somehow query this field via Prisma, to return rows where the field contains one or more hotel ids. Does anyone know how this can be achieves with Prisma? I can only seem to find ways to query on actual data, not on path existence or path data presence.

Worst case I could accept just checking if the "hotels" object is in the json, without actually checking for presence of child ids.

Answer 1

You could use the queryRaw prisma method for passing raw queries and then checking for path existence.

You can pass the below select query to check if the hotels key exists.

SELECT '{"hotels":{"id":984}}'::jsonb ? 'hotels'

The query would return true if the hotels key exists.