[BUG] Search for null values/ existence check of attributes in json is not working

[CollinKempkes]

Description and expected behavior When I want to search for DbNull values (search for existence of an JSON attribute inside a JSON field) inside a find operation, zenstack seems to encounter some problems so that it will return an empty resultset.

For better understanding here is an example of the isolated issue:

test.zmodel

model Test {
  id       String @id @default(dbgenerated("uuid_generate_v4()")) @db.Uuid
  metadata Json

  @@map("test")
  @@schema("public")

  @@allow('all', true)
}

This is the suite I'm running:

test.spec

import { PrismaClient } from '@equitybytes/nest-prisma';
import { Prisma, Test } from '@prisma/client';
import { ExtendedPrismaClient } from '@vdrip-database/database.types';
import {
  enhanceClientWithUser,
  extendClient,
} from '@vdrip-database/database.util';
import { clearDbWithPrismaClient } from 'src/test-utils/db.test-helper';

describe('Test', () => {
  let testWithMetadata: Test;
  let testWithEmptyMetadata: Test;
  let extendedClient: ExtendedPrismaClient;
  let enhancedClient: ExtendedPrismaClient;

  beforeAll(async () => {
    const prismaClient = new PrismaClient();
    extendedClient = extendClient(prismaClient);
    await clearDbWithPrismaClient(extendedClient);

    const plattformUser =
      await extendedClient.user.findFirstPermissionsAndRestrictions({
        where: {
          id: process.env['VDRIP_PLATFORM_USER_ID'] as string,
        },
      });
    enhancedClient = enhanceClientWithUser(prismaClient, plattformUser);
  });

  beforeEach(async () => {
    await clearDbWithPrismaClient(extendedClient);

    testWithMetadata = await enhancedClient.test.create({
      data: {
        metadata: {
          test: 'test',
        },
      },
    });
    testWithEmptyMetadata = await enhancedClient.test.create({
      data: {
        metadata: {},
      },
    });
  });

  describe('search for null value', () => {
    it('should work with extended client', async () => {
      const result = await extendedClient.test.findMany({
        where: {
          metadata: {
            path: ['test'],
            equals: Prisma.DbNull,
          },
        },
      });

      expect(result).toHaveLength(1);
      expect(result).toEqual(
        expect.arrayContaining([
          expect.objectContaining({ id: testWithEmptyMetadata.id }),
        ])
      );
    });

    it('should work with enhanced client', async () => {
      const result = await enhancedClient.test.findMany({
        where: {
          metadata: {
            path: ['test'],
            equals: Prisma.DbNull,
          },
        },
      });

      expect(result).toHaveLength(1);
      expect(result).toEqual(
        expect.arrayContaining([
          expect.objectContaining({ id: testWithEmptyMetadata.id }),
        ])
      );
    });
  });

  describe('search for set value', () => {
    it('should work with extended client', async () => {
      const result = await extendedClient.test.findMany({
        where: {
          metadata: {
            path: ['test'],
            equals: 'test',
          },
        },
      });

      expect(result).toHaveLength(1);
      expect(result).toEqual(
        expect.arrayContaining([
          expect.objectContaining({ id: testWithMetadata.id }),
        ])
      );
    });

    it('should work with enhanced client', async () => {
      const result = await enhancedClient.test.findMany({
        where: {
          metadata: {
            path: ['test'],
            equals: 'test',
          },
        },
      });

      expect(result).toHaveLength(1);
      expect(result).toEqual(
        expect.arrayContaining([
          expect.objectContaining({ id: testWithMetadata.id }),
        ])
      );
    });
  });
});

And this is the output of the test:

FAIL   api  apps/api/src/app/stripe/test.spec.ts (94 MB heap size)
  Test
    search for null value
      ✓ should work with extended client (138 ms)
      ✕ should work with enhanced client (159 ms)
    search for set value
      ✓ should work with extended client (146 ms)
      ✓ should work with enhanced client (143 ms)

  ● Test › search for null value › should work with enhanced client

    expect(received).toHaveLength(expected)

    Expected length: 1
    Received length: 0
    Received array:  []

      74 |       });
      75 |
    > 76 |       expect(result).toHaveLength(1);
         |                      ^
      77 |       expect(result).toEqual(
      78 |         expect.arrayContaining([
      79 |           expect.objectContaining({ id: testWithEmptyMetadata.id }),

      at Object.<anonymous> (src/app/stripe/test.spec.ts:76:22)

Test Suites: 1 failed, 1 total
Tests:       1 failed, 3 passed, 4 total
Snapshots:   0 total
Time:        1.784 s, estimated 5 s

So I think it definitely is a problem inside zenstack as the extended client is working well. If its not possible to fix this problem, is there another workaround to search for not existing json attributes inside a json field?

We could also do it in plain JS afterwards, but it would be a way better solution in our code if this would work.

Environment (please complete the following information):

• ZenStack version: 2.1.2
• Prisma version: 5.13.0
• Database type: Postgresql

[ymc9]

Hi @CollinKempkes , it seems there's some problem dealing with Prisma.DbNull. Let me debug and figure out what exactly caused it. Thanks for filing the bug!