---
title: 'Working with Json fields'
metaTitle: 'Working with Json fields (Concepts)'
metaDescription: 'How to read, write, and filter by Json fields.'
tocDepth: 3
---

Use the [`Json`](/orm/reference/prisma-schema-reference#json) Prisma ORM field type to read, write, and perform basic filtering on JSON types in the underlying database. In the following example, the `User` model has an optional `Json` field named `extendedPetsData`:

```prisma highlight=6;normal
model User {
  id               Int     @id @default(autoincrement())
  email            String  @unique
  name             String?
  posts            Post[]
  //highlight-next-line
  extendedPetsData Json?
}
```

Example field value:

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


The `Json` field supports a few additional types, such as `string` and `boolean`. These additional types exist to match the types supported by [`JSON.parse()`](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/JSON/parse):

```ts
export type JsonValue =
  | string
  | number
  | boolean
  | null
  | JsonObject
  | JsonArray
```

## Use cases for JSON fields

Reasons to store data as JSON rather than representing data as related models include:

- You need to store data that does not have a consistent structure
- You are importing data from another system and do not want to map that data to Prisma models

## Reading a `Json` field

You can use the `Prisma.JsonArray` and `Prisma.JsonObject` utility classes to work with the contents of a `Json` field:

```ts
const { PrismaClient, Prisma } = require('@prisma/client')

const user = await prisma.user.findFirst({
  where: {
    id: 9,
  },
})

// Example extendedPetsData data:
// [{ name: 'Bob the dog' }, { name: 'Claudine the cat' }]

if (
  user?.extendedPetsData &&
  typeof user?.extendedPetsData === 'object' &&
  Array.isArray(user?.extendedPetsData)
) {
  const petsObject = user?.extendedPetsData as Prisma.JsonArray

  const firstPet = petsObject[0]
}
```

See also: [Advanced example: Update a nested JSON key value](#advanced-example-update-a-nested-json-key-value)

## Writing to a `Json` field

The following example writes a JSON object to the `extendedPetsData` field:

```ts
var json = [
  { name: 'Bob the dog' },
  { name: 'Claudine the cat' },
] as Prisma.JsonArray

const createUser = await prisma.user.create({
  data: {
    email: 'birgitte@prisma.io',
    extendedPetsData: json,
  },
})
```

> **Note**: JavaScript objects (for example, `{ extendedPetsData: "none"}`) are automatically converted to JSON.

See also: [Advanced example: Update a nested JSON key value](#advanced-example-update-a-nested-json-key-value)

## Filter on a `Json` field (simple)

You can filter rows of `Json` type.

### Filter on exact field value

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

```ts
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:

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

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

## Filter on a `Json` field (advanced)

You can also filter rows by the data inside a `Json` field. We call this **advanced `Json` filtering**. This functionality is supported by [PostgreSQL](/orm/overview/databases/postgresql) and [MySQL](/orm/overview/databases/mysql) only with [different syntaxes for the `path` option](#path-syntax-depending-on-database).

:::warning

PostgreSQL does not support [filtering on object key values in arrays](#filtering-on-object-key-value-inside-array).

:::

:::info

The availability of advanced `Json` filtering depends on your Prisma version:

- v4.0.0 or later: advanced `Json` filtering is [generally available](/orm/more/releases#generally-available-ga).
- From v2.23.0, but before v4.0.0: advanced `Json` filtering is a [preview feature](/orm/reference/preview-features/client-preview-features). Add `previewFeatures = ["filterJson"]` to your schema. [Learn more](/orm/reference/preview-features/client-preview-features#enabling-a-prisma-client-preview-feature).
- Before v2.23.0: you can [filter on the exact `Json` field value](#filter-on-exact-field-value), but you cannot use the other features described in this section.

:::

### `path` syntax depending on database

The filters below use a `path` option to select specific parts of the `Json` value to filter on. The implementation of that filtering differs between connectors:

- The [MySQL connector](/orm/overview/databases/mysql) uses [MySQL's implementation of JSON path](https://dev.mysql.com/doc/refman/8.0/en/json.html#json-path-syntax)
- The [PostgreSQL connector](/orm/overview/databases/postgresql) uses the custom JSON functions and operators [supported in version 12 _and earlier_](https://www.postgresql.org/docs/11/functions-json.html)

For example, the following is a valid MySQL `path` value:

```
$petFeatures.petName
```

The following is a valid PostgreSQL `path` value:

```
["petFeatures", "petName"]
```

### Filter on object property

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:

```json highlight=11;normal
{
  "petName": "Claudine",
  "petType": "House cat"
}
```

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

<TabbedContent code>
<TabItem value="PostgreSQL">

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

</TabItem>
<TabItem value="MySQL">

```ts
const getUsers = await prisma.user.findMany({
  where: {
    extendedPetsData: {
      path: '$.petName',
      equals: 'Claudine',
    },
  },
})
```

</TabItem>

</TabbedContent>

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

<TabbedContent code>
<TabItem value="PostgreSQL">

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

</TabItem>
<TabItem value="MySQL">

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

</TabItem>
</TabbedContent>

The following string filters are available:

- [`string_contains`](/orm/reference/prisma-client-reference#string_contains)
- [`string_starts_with`](/orm/reference/prisma-client-reference#string_starts_with)
- [`string_ends_with`](/orm/reference/prisma-client-reference#string_ends_with) .

To use case insensitive filter with these, you can use the [`mode`](/orm/reference/prisma-client-reference#mode) option:

<TabbedContent code>
<TabItem value="PostgreSQL">

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

</TabItem>
<TabItem value="MySQL">

```ts
const getUsers = await prisma.user.findMany({
  where: {
    extendedPetsData: {
      path: '$.petType',
      string_contains: 'cat',
      mode: 'insensitive'
    },
  },
})
```

</TabItem>
</TabbedContent>

### 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.

```json
{
  "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"` &rarr; `"petName"` is `"Sunny"`:

<TabbedContent code>
<TabItem value="PostgreSQL">

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

</TabItem>
<TabItem value="MySQL">

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

</TabItem>
</TabbedContent>

The following query returns all users where:

- `"pet2"` &rarr; `"petName"` is `"Sunny"`
- `"pet2"` &rarr; `"features"` &rarr; `"furColor"` contains `"black"`

<TabbedContent code>
<TabItem value="PostgreSQL">

```ts
const getUsers = await prisma.user.findMany({
  where: {
    AND: [
      {
        extendedPetsData: {
          path: ['pet2', 'petName'],
          equals: 'Sunny',
        },
      },
      {
        extendedPetsData: {
          path: ['pet2', 'features', 'furColor'],
          string_contains: 'black',
        },
      },
    ],
  },
})
```

</TabItem>
<TabItem value="MySQL">

```ts
const getUsers = await prisma.user.findMany({
  where: {
    AND: [
      {
        extendedPetsData: {
          path: '$.pet2.petName',
          equals: 'Sunny',
        },
      },
      {
        extendedPetsData: {
          path: '$.pet2.features.furColor',
          string_contains: 'black',
        },
      },
    ],
  },
})
```

</TabItem>

</TabbedContent>

### 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:

```json
["Claudine", "Sunny"]
```

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

<TabbedContent code>
<TabItem value="PostgreSQL">

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

:::info

In PostgreSQL, the value of `array_contains` must be an array and not a string, even if the array only contains a single value.

:::

</TabItem>
<TabItem value="MySQL">

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

</TabItem>

</TabbedContent>

The following array filters are available:

- [`array_contains`](/orm/reference/prisma-client-reference#array_contains)
- [`array_starts_with`](/orm/reference/prisma-client-reference#array_starts_with)
- [`array_ends_with`](/orm/reference/prisma-client-reference#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:

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

#### Scalar value arrays

The following query returns all users that foster a cat named `"Fido"`:

<TabbedContent code>
<TabItem value="PostgreSQL">

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

:::info

In PostgreSQL, the value of `array_contains` must be an array and not a string, even if the array only contains a single value.

:::

</TabItem>
<TabItem value="MySQL">

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

</TabItem>
</TabbedContent>

The following query returns all users that foster cats named `"Fido"` _and_ `"Bob"`:

<TabbedContent code>
<TabItem value="PostgreSQL">

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

</TabItem>
<TabItem value="MySQL">

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

</TabItem>
</TabbedContent>

#### JSON object arrays

<TabbedContent code>
<TabItem value="PostgreSQL">

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

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

</TabItem>
<TabItem value="MySQL">

```ts
const json = { status: 'expired', insuranceID: 92 }

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

</TabItem>
</TabbedContent>

- If you are using PostgreSQL, you must pass in an array of objects to match, even if that array only contains one object:

  ```json5
  [{ status: 'expired', insuranceID: 92 }]
  // PostgreSQL
  ```

  If you are using MySQL, you must pass in a single object to match:

  ```json5
  { status: 'expired', insuranceID: 92 }
  // MySQL
  ```

- If your filter array contains multiple objects, PostgreSQL will only return results if _all_ objects are present - not if at least one object is present.

- You must set `array_contains` to a JSON object, not a string. If you use a string, Prisma Client escapes the quotation marks and the query will not return results. For example:

  ```ts
  array_contains: '[{"status": "expired", "insuranceID": 92}]'
  ```

  is sent to the database as:

  ```
  [{\"status\": \"expired\", \"insuranceID\": 92}]
  ```

### Targeting an array element by index

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

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

<TabbedContent code>
<TabItem value="PostgreSQL">

```ts
const getUsers = await prisma.user.findMany({
  where: {
    comments: {
      path: ['owned', '1'],
      string_contains: 'Bob',
    },
  },
})
```

</TabItem>

<TabItem value="MySQL">

```ts
const getUsers = await prisma.user.findMany({
  where: {
    comments: {
      path: '$.owned[1]',
      string_contains: 'Bob',
    },
  },
})
```

</TabItem>

</TabbedContent>

### Filtering on object key value inside array

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

:::warning

Filtering on object key values within an array is **only** supported by the [MySQL database connector](/orm/overview/databases/mysql). However, you can still [filter on the presence of entire JSON objects](#json-object-arrays).

:::

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

```json
[
  {
    "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:

```ts
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:

```ts
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

## Advanced example: Update a nested JSON key value

The following example assumes that the value of `extendedPetsData` is some variation of the following:

```json
{
  "petName": "Claudine",
  "petType": "House cat",
  "insurances": [
    { "insuranceID": 92, "status": "expired" },
    { "insuranceID": 12, "status": "active" }
  ]
}
```

The following example:

1. Gets all users
1. Change the `"status"` of each insurance object to `"expired"`
1. Get all users that have an expired insurance where the ID is `92`

<TabbedContent code>
<TabItem value="PostgreSQL">

```ts
const userQueries: string | any[] = []

getUsers.forEach((user) => {
  if (
    user.extendedPetsData &&
    typeof user.extendedPetsData === 'object' &&
    !Array.isArray(user.extendedPetsData)
  ) {
    const petsObject = user.extendedPetsData as Prisma.JsonObject

    const i = petsObject['insurances']

    if (i && typeof i === 'object' && Array.isArray(i)) {
      const insurancesArray = i as Prisma.JsonArray

      insurancesArray.forEach((i) => {
        if (i && typeof i === 'object' && !Array.isArray(i)) {
          const insuranceObject = i as Prisma.JsonObject

          insuranceObject['status'] = 'expired'
        }
      })

      const whereClause = Prisma.validator<Prisma.UserWhereInput>()({
        id: user.id,
      })

      const dataClause = Prisma.validator<Prisma.UserUpdateInput>()({
        extendedPetsData: petsObject,
      })

      userQueries.push(
        prisma.user.update({
          where: whereClause,
          data: dataClause,
        })
      )
    }
  }
})

if (userQueries.length > 0) {
  console.log(userQueries.length + ' queries to run!')
  await prisma.$transaction(userQueries)
}

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

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

console.log(checkJson.length)
```

</TabItem>

<TabItem value="MySQL">

```ts
const userQueries: string | any[] = []

getUsers.forEach((user) => {
  if (
    user.extendedPetsData &&
    typeof user.extendedPetsData === 'object' &&
    !Array.isArray(user.extendedPetsData)
  ) {
    const petsObject = user.extendedPetsData as Prisma.JsonObject

    const insuranceList = petsObject['insurances'] // is a Prisma.JsonArray

    if (Array.isArray(insuranceList)) {
      insuranceList.forEach((insuranceItem) => {
        if (
          insuranceItem &&
          typeof insuranceItem === 'object' &&
          !Array.isArray(insuranceItem)
        ) {
          insuranceItem['status'] = 'expired' // is a  Prisma.JsonObject
        }
      })

      const whereClause = Prisma.validator<Prisma.UserWhereInput>()({
        id: user.id,
      })

      const dataClause = Prisma.validator<Prisma.UserUpdateInput>()({
        extendedPetsData: petsObject,
      })

      userQueries.push(
        prisma.user.update({
          where: whereClause,
          data: dataClause,
        })
      )
    }
  }
})

if (userQueries.length > 0) {
  console.log(userQueries.length + ' queries to run!')
  await prisma.$transaction(userQueries)
}

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

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

console.log(checkJson.length)
```

</TabItem>
</TabbedContent>

## Using `null` Values

There are two types of `null` values possible for a `JSON` field in an SQL database.

- Database `NULL`: The value in the database is a `NULL`.
- JSON `null`: The value in the database contains a JSON value that is `null`.

To differentiate between these possibilities, we've introduced three _null enums_ you can use:

- `JsonNull`: Represents the `null` value in JSON.
- `DbNull`: Represents the `NULL` value in the database.
- `AnyNull`: Represents both `null` JSON values and `NULL` database values. (Only when filtering)

:::info

From v4.0.0, `JsonNull`, `DbNull`, and `AnyNull` are objects. Before v4.0.0, they were strings.

:::

:::info

- When filtering using any of the _null enums_ you can not use a shorthand and leave the `equals` operator off.
- These _null enums_ do not apply to MongoDB because there the difference between a JSON `null` and a database `NULL` does not exist.
- The _null enums_ do not apply to the `array_contains` operator in all databases because there can only be a JSON `null` within a JSON array. Since there cannot be a database `NULL` within a JSON array, `{ array_contains: null }` is not ambiguous.

:::
  
For example:

```prisma
model Log {
  id   Int  @id
  meta Json
}
```

Here is an example of using `AnyNull`:

```ts highlight=7;normal
import { Prisma } from '@prisma/client'

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

### Inserting `null` Values

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

```ts highlight=5;normal
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:

```ts highlight=5;normal
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:

```ts highlight=6;normal
import { Prisma } from '@prisma/client'

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

:::info

These _null enums_ do not apply to MongoDB because MongoDB does not differentiate between a JSON `null` and a database `NULL`. They also do not apply to the `array_contains` operator in all databases because there can only be a JSON `null` within a JSON array. Since there cannot be a database `NULL` within a JSON array, `{ array_contains: null }` is not ambiguous.

:::

## Typed `Json` Fields

Prisma's `Json` fields are untyped by default. To add strong typing, you can use the external package [prisma-json-types-generator](https://www.npmjs.com/package/prisma-json-types-generator).

1.  First, install the package and add the generator to your `schema.prisma`:

    ```bash
    npm install -D prisma-json-types-generator
    ```

    ```prisma file=schema.prisma
    generator client {
      provider = "prisma-client"
      output   = "./generated"
    }

    generator json {
      provider = "prisma-json-types-generator"
    }
    ```

2.  Next, link a field to a TypeScript type using an [AST comment](/orm/prisma-schema/overview#comments).

    ```prisma highlight=4;normal file=schema.prisma showLineNumbers
    model Log {
      id   Int @id

      //highlight-next-line
      /// [LogMetaType]
      meta Json
    }
    ```

3.  Then, define `LogMetaType` in a type declaration file (e.g., `types.ts`) that is included in your `tsconfig.json`.

    ```ts file=types.ts showLineNumbers
    declare global {
      namespace PrismaJson {
        type LogMetaType = { timestamp: number; host: string };
      }
    }

    // This file must be a module.
    export {};
    ```

Now, `Log.meta` will be strongly typed as `{ timestamp: number; host: string }`.

### Typing `String` Fields and Advanced Features

You can also apply these techniques to `String` fields. This is especially useful for creating string-based enums directly in your schema when your database does not support enum types.

```prisma
model Post {
  id     Int    @id

  /// !['draft' | 'published']
  status String

  /// [LogMetaType]
  meta   Json[]
}
```

This results in `post.status` being strongly typed as `'draft' | 'published'` and `post.meta` as `LogMetaType[]`.

For a complete guide on configuration, monorepo setup, and other advanced features, please refer to the [official `prisma-json-types-generator` documentation](https://github.com/arthurfiorette/prisma-json-types-generator#readme).

## `Json` FAQs

### Can you select a subset of JSON key/values to return?

No - it is not yet possible to [select which JSON elements to return](https://github.com/prisma/prisma/issues/2431). Prisma Client returns the entire JSON object.

### Can you filter on the presence of a specific key?

No - it is not yet possible to filter on the presence of a specific key.

### Is case insensitive filtering supported?

No - [case insensitive filtering](https://github.com/prisma/prisma/issues/7390) is not yet supported.

### Can you sort an object property within a JSON value?

No, [sorting object properties within a JSON value](https://github.com/prisma/prisma/issues/10346) (order-by-prop) is not currently supported.

### How to set a default value for JSON fields?

When you want to set a `@default` value the `Json` type, you need to enclose it with double-quotes inside the `@default` attribute (and potentially escape any "inner" double-quotes using a backslash), for example:

```prisma
model User {
  id    Int  @id @default(autoincrement())
  json1 Json @default("[]")
  json2 Json @default("{ \"hello\": \"world\" }")
}
```
