Database connector implementation differences

The implementation of Json filtering differs between connectors:

• The MySQL connector uses MySQL's implementation of JSON path
• The PostgreSQL connector uses the custom JSON functions and operators supported in version 12 and earlier

This means that path option syntax differs between database connectors - for example, the following is a valid MySQL path value:

$petFeatures.petName

The following is a valid PostgreSQL path value:

["petFeatures", "petName"]

Filter on exact field value

The following query returns all users where the value of extendedPetsData matches the json variable exactly:

var json = { [{ name: 'Bob the dog' }, { name: 'Claudine the cat' }] }

const getUsers = await prisma.user.findMany({
  where: {
    extendedPetsData: {
      equals: json,
    },




    
  },
})

The following query returns all users where the value of extendedPetsData does not match the json variable exactly:

var json = {
  extendedPetsData: [{ name: 'Bob the dog' }, { name: 'Claudine the cat' }],
}

const getUsers = await prisma.user.findMany({
  where: {
    extendedPetsData: {
      not: json,
    },
  },
})

Filter on object property

In 2.23.0 and later, you can filter on a specific property inside a block of JSON. In the following examples, the value of extendedPetsData is a one-dimensional, unnested JSON object:

{
  "petName": "Claudine",
  "petType": "House cat"
}

The following query returns all users where the value of petName is "Claudine" :

const getUsers = await prisma.user.findMany({




    
  where: {
    extendedPetsData: {
      path: ['petName'],
      equals: 'Claudine',
    },
  },
})

The following query returns all users where the value of petType contains "cat" :

const getUsers = await prisma.user.findMany({
  where: {
    extendedPetsData: {
      path: ['petType'],
      string_contains: 'cat',
    },
  },
})

The following string filters are available:

• string_contains
• string_starts_with
• string_ends_with .

Filter on nested object property

You can filter on nested JSON properties. In the following examples, the value of extendedPetsData is a JSON object with several levels of nesting.

{
  "pet1": {
    "petName": "Claudine",
    "petType": "House cat"
  },
  "pet2": {
    "petName": "Sunny",
    "petType": "Gerbil",




    
    "features": {
      "eyeColor": "Brown",
      "furColor": "White and black"
    }
  }
}

The following query returns all users where "pet2" → "petName" is "Sunny" :

const getUsers = await prisma.user.findMany({
  where: {
    extendedPetsData: {
      path: ['pet2', 'petName'],
      equals: 'Sunny',
    },
  },
})

The following query returns all users where:

• "pet2" → "petName" is "Sunny"
• "pet2" → "features" → "furColor" contains "black"

const getUsers = await prisma.user.findMany({
  where: {
    AND: [
      {
        extendedPetsData: {
          path: ['pet2', 'petName'],




    
          equals: 'Sunny',
        },
      },
      {
        extendedPetsData: {
          path: ['pet2', 'features', 'furColor'],
          string_contains: 'black',
        },
      },
    ],
  },
})

Filtering on an array value

You can filter on the presence of a specific value in a scalar array (strings, integers). In the following example, the value of extendedPetsData is an array of strings:

["Claudine", "Sunny"]

The following query returns all users with a pet named "Claudine" :

const getUsers = await prisma.user.findMany({
  where: {
    extendedPetsData: {
      array_contains: ['Claudine'],
    },
  },
})

The following array filters are available:

• array_contains
• array_starts_with
• array_ends_with

Filtering on nested array value

You can filter on the presence of a specific value in a scalar array (strings, integers). In the following examples, the value of extendedPetsData includes nested scalar arrays of names:

    
{
  "cats": { "owned": ["Bob", "Sunny"], "fostering": ["Fido"] },
  "dogs": { "owned": ["Ella"], "fostering": ["Prince", "Empress"] }
}

const getUsers = await prisma.user.findMany({
  where: {
    extendedPetsData: {
      path: ['cats', 'fostering'],
      array_contains: ['Fido'],
    },
  },
})

    
const getUsers = await prisma.user.findMany({
  where: {
    extendedPetsData: {
      path: ['cats', 'fostering'],
      array_contains: ['Fido', 'Bob'],
    },
  },
})

const json = [{ status: 'expired', insuranceID: 92 }]

const checkJson = await prisma.user.findMany({
  where: {
    extendedPetsData: {
      path: ['insurances'],
      array_contains: json,
    },
  },
})

Targeting an array element by index

You can filter on the value of an element in a specific position.

{ "owned": ["Bob", "Sunny"], "fostering": ["Fido"] }

const getUsers = await prisma.user.findMany({




    
  where: {
    comments: {
      path: ['owned', '1'],
      string_contains: 'Bob',
    },
  },
})

Filtering on object key value inside array

Depending on your provider, you can filter on the key value of an object inside an array.

In the following example, the value of extendedPetsData is an array of objects with a nested insurances array, which contains two objects:

[
  {
    "petName": "Claudine",
    "petType": "House cat",
    "insurances": [
      { "insuranceID": 92, "status": "expired" },
      { "insuranceID": 12, "status": "active" }
    ]
  },
  {
    "petName": "Sunny",
    "petType": "Gerbil"
  },
  {
    "petName": "Gerald",
    "petType": "Corn snake"
  },
  {
    "petName": "Nanna",
    "petType": "Moose"
  }
]

The following query returns all users where at least one pet is a moose:

    
const getUsers = await prisma.user.findMany({
  where: {
    extendedPetsData: {
      path: '$[*].petType',
      array_contains: 'Moose',
    },
  },
})

• $[*] is the root array of pet objects
• petType matches the petType key in any pet object

The following query returns all users where at least one pet has an expired insurance:

const getUsers = await prisma.user.findMany({
  where: {
    extendedPetsData: {
      path: '$[*].insurances[*].status',
      array_contains: 'expired',
    },
  },
})

• $[*] is the root array of pet objects
• insurances[*] matches any insurances array inside any pet object
• status matches any status key in any insurance object

Inserting null Values

This also applies to create , update and upsert . To insert a null value into a Json field, you would write:

import { Prisma } from '@prisma/client'

prisma.log.create({




    
  data: {
    meta: Prisma.JsonNull,
  },
})

And to insert a database NULL into a Json field, you would write:

import { Prisma } from '@prisma/client'

prisma.log.create({
  data: {
    meta: Prisma.DbNull,
  },
})

Filtering by null Values

To filter by JsonNull or DbNull , you would write:

import { Prisma } from '@prisma/client'

prisma.log.findMany({
  where: {
    meta: {
      equals: Prisma.AnyNull,
    },
  },
})