> For the complete documentation index, see [llms.txt](https://docs.pyck.cloud/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.pyck.cloud/graphql-api/advanced/json-field-queries.md).

# JSON Field Queries

Learn how to query and filter entities based on their flexible JSON data fields in pyck

Audience: Programmer

pyck provides powerful JSON field querying capabilities through GraphQL, allowing you to filter entities based on values stored in their flexible `data` fields. This guide covers all aspects of querying JSON data in pyck.

## Overview

Many entities in pyck include a flexible JSON `data` field that can store arbitrary structured data. This field is part of the DataMixin and is available on entities like:

* Customers
* Suppliers
* Inventory Items
* Workflows
* Orders
* And many more...

The JSON query system allows you to:

* Search for specific values at any path in the JSON structure
* Check for the existence of keys
* Match values against lists
* Check whether an array holds an element
* Combine multiple JSON queries with logical operators

## Available JSON Query Operations

### 1. Data - Exact Value Match

The `Data` operation checks if a value at a specific JSON path equals the provided value.

**Syntax**: `Data: ["path", "value"]`

`Data` compares the text rendering of the value at the path with the given string. The match is exact and case-sensitive. Because both sides are text, it cannot distinguish a stored number `246` from a stored string `"246"`: `Data: ["a", "246"]` matches both.

**Example**: Find customers where `data.name` equals "Max"

```graphql
query {
  customers(where: {
    Data: ["name", "Max"]
  }) {
    edges {
      node {
        id
        data
      }
    }
  }
}
```

**Nested path example**: Find items where `data.specifications.color` equals "red"

```graphql
query {
  inventoryItems(where: {
    Data: ["specifications.color", "red"]
  }) {
    edges {
      node {
        id
        sku
        data
      }
    }
  }
}
```

### 2. DataHasKey - Check Key Existence

The `DataHasKey` operation checks if a specific key exists in the JSON structure.

**Syntax**: `DataHasKey: "path"`

**Example**: Find customers that have a "vipStatus" field

```graphql
query {
  customers(where: {
    DataHasKey: "vipStatus"
  }) {
    edges {
      node {
        id
        data
      }
    }
  }
}
```

**Nested path example**: Find items that have weight specifications

```graphql
query {
  inventoryItems(where: {
    DataHasKey: "specifications.weight"
  }) {
    edges {
      node {
        id
        sku
        data
      }
    }
  }
}
```

### 3. DataIn - Value in List

The `DataIn` operation checks if the value at a JSON path is one of the provided values.

**Syntax**: `DataIn: ["path", "value1", "value2", ...]`

**Example**: Find workflows with specific statuses

```graphql
query {
  workflows(where: {
    DataIn: ["status", "pending", "processing", "completed"]
  }) {
    edges {
      node {
        id
        data
      }
    }
  }
}
```

### 4. DataContains - Containment at a Path

The `DataContains` operation takes exactly two elements, a JSON path and a value, and checks with PostgreSQL jsonb containment (`@>`) whether the value at that path contains the given value. It addresses a single path, not a fragment of the `data` object. The value is always sent as a JSON string, so the result depends on what is stored at the path:

| Stored at the path        | Matches when                                                                                  |
| ------------------------- | --------------------------------------------------------------------------------------------- |
| Array                     | The array holds the value as a string element (an array of numbers or booleans never matches) |
| String                    | The string equals the value exactly (case-sensitive)                                          |
| Number, boolean or object | Never: a JSON string is never contained in a number, boolean or object                        |

**Syntax**: `DataContains: ["path", "value"]`

A filter with any other number of elements fails with an `invalid JSON data filter` error.

There is no substring or prefix matching on a JSON path: `DataContains: ["company", "Tech"]` does not match `"company": "Tech Corp"`. Do not confuse it with the `contains` operator of the [`dataIndex`](https://docs.pyck.cloud/graphql-api/advanced/data-indices.md) filter, a substring match on a text index slot.

**Example**: Find customers tagged "premium" (`"tags": ["premium", "verified"]`)

```graphql
query {
  customers(where: {
    DataContains: ["tags", "premium"]
  }) {
    edges {
      node {
        id
        data
      }
    }
  }
}
```

## JSON Path Syntax

pyck uses dot notation for JSON paths, supporting:

### Simple Paths

* `"name"` - Top-level field
* `"email"` - Another top-level field

### Nested Paths

* `"address.city"` - Nested object field
* `"contact.phone.mobile"` - Deeply nested field

### Array Access

* `"tags[0]"` - First element of an array
* `"addresses[1].city"` - City field of second address

### Examples of JSON structures and their paths:

Given this JSON data:

```json
{
  "name": "Max",
  "company": "Tech Corp",
  "address": {
    "street": "123 Main St",
    "city": "Berlin",
    "country": "Germany"
  },
  "tags": ["premium", "verified", "corporate"],
  "contacts": [
    {
      "type": "email",
      "value": "max@techcorp.com"
    },
    {
      "type": "phone",
      "value": "+49123456789"
    }
  ]
}
```

You can query:

* `"name"` → "Max"
* `"address.city"` → "Berlin"
* `"tags[0]"` → "premium"
* `"contacts[1].value"` → "+49123456789"

## Complex Queries

### Combining Multiple JSON Conditions

You can combine multiple JSON queries using logical operators:

```graphql
query {
  customers(where: {
    and: [
      { Data: ["status", "active"] },
      { DataHasKey: "vipLevel" },
      { DataContains: ["tags", "premium"] }
    ]
  }) {
    edges {
      node {
        id
        data
      }
    }
  }
}
```

### Combining JSON Queries with Other Fields

Mix JSON queries with regular field queries:

```graphql
query {
  inventoryItems(where: {
    and: [
      { skuContainsFold: "PHONE" },
      { Data: ["category", "electronics"] },
      { DataHasKey: "specifications.warranty" }
    ]
  }) {
    edges {
      node {
        id
        sku
        data
      }
    }
  }
}
```

### Using OR Logic

Find items matching any of several JSON conditions:

```graphql
query {
  inventoryItems(where: {
    or: [
      { Data: ["color", "red"] },
      { Data: ["color", "blue"] },
      { Data: ["color", "green"] }
    ]
  }) {
    edges {
      node {
        id
        sku
        data
      }
    }
  }
}
```

## Real-World Examples

### Example 1: Find Premium Customers in a Specific Region

```graphql
query {
  customers(where: {
    and: [
      { Data: ["tier", "premium"] },
      { Data: ["region", "EMEA"] },
      { DataContains: ["tags", "corporate"] }
    ]
  }) {
    edges {
      node {
        id
        data
      }
    }
  }
}
```

### Example 2: Find Items with Specific Attributes

```graphql
query {
  inventoryItems(where: {
    and: [
      { DataHasKey: "specifications" },
      { Data: ["specifications.material", "aluminum"] },
      { DataIn: ["specifications.size", "S", "M", "L"] }
    ]
  }) {
    edges {
      node {
        id
        sku
        data
      }
    }
  }
}
```

### Example 3: Complex Workflow Filtering

```graphql
query {
  workflows(where: {
    and: [
      { Data: ["type", "order_processing"] },
      { DataIn: ["status", "pending", "processing"] },
      { DataHasKey: "metadata.priority" }
    ]
  }) {
    totalCount
    edges {
      node {
        id
        data
      }
    }
  }
}
```

## Performance Considerations

1. **Indexing**: JSON path filters do not use an index. Each one extracts the path from `data` in every row it compares, so it scans the tenant's table. That is fine for small result sets and ad-hoc queries, but unsuitable for lookups over large tables or lookups that name many values at once. For those, declare the key as a data index on entities that support it and query it with `dataIndex`: see [Data Indices](https://docs.pyck.cloud/graphql-api/advanced/data-indices.md).
2. **Path Depth**: Deeper paths may have slightly slower performance
3. **Array Access**: Queries on array elements are generally slower than object field queries
4. **Combining Queries**: Use `and` arrays efficiently - order conditions from most to least selective

## Best Practices

1. **Use Specific Paths**: More specific paths perform better than searching entire JSON structures
2. **Combine with Regular Filters**: Use standard field filters (like `sku`, `createdAt`) alongside JSON queries for better performance
3. **Validate JSON Structure**: Ensure your JSON data follows a consistent structure for reliable querying
4. **Use DataHasKey First**: When filtering optional fields, check existence before value comparison

## JavaScript/TypeScript Examples

### Basic Query

```javascript
const query = `
  query GetCustomersByName($name: String!) {
    customers(where: {
      Data: ["name", $name]
    }) {
      edges {
        node {
          id
          data
        }
      }
    }
  }
`;

const variables = { name: "Max" };
const result = await graphqlClient.request(query, variables);
```

### Complex Query Builder

```javascript
function buildCustomerQuery(filters) {
  const whereConditions = [];
  
  if (filters.name) {
    whereConditions.push({ Data: ["name", filters.name] });
  }
  
  if (filters.hasVipStatus) {
    whereConditions.push({ DataHasKey: "vipStatus" });
  }
  
  if (filters.regions && filters.regions.length > 0) {
    whereConditions.push({ 
      DataIn: ["region", ...filters.regions] 
    });
  }
  
  return {
    where: whereConditions.length > 1 
      ? { and: whereConditions }
      : whereConditions[0]
  };
}
```

## Troubleshooting

### Common Issues

1. **No results returned**: Check that your JSON path matches the exact structure of your data
2. **Case sensitivity**: JSON queries are case-sensitive by default
3. **Type mismatches**: Values are always sent as strings. `Data` compares text renderings, so `Data: ["a", "246"]` matches both `246` and `"246"`; there is no numeric comparison
4. **`DataContains` returns nothing or fails**: It matches only a string element of an array or an exact string, never a stored number, boolean or object (use `Data` for a number or boolean). Any filter other than `["path", "value"]` fails with `invalid JSON data filter`

### Debugging Tips

1. First query without JSON filters to see the data structure
2. Use `DataHasKey` to verify paths exist
3. Start with simple queries and build complexity gradually

## Related Topics

* [Inputs](https://docs.pyck.cloud/graphql-api/inputs.md) - All input types, including the `*WhereInput` filters
* [Data Types](https://docs.pyck.cloud/graphql-api/queries/datatypes.md) - Managing data type schemas


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation by asking a question.

Perform an HTTP GET request on the following URL with the `ask` and `goal` query parameters:

```
GET https://docs.pyck.cloud/graphql-api/advanced/json-field-queries.md?ask=<question>&goal=<user_goal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is what the user is ultimately trying to achieve, the reason they need the answer. Sharing it helps GitBook give you a better, more relevant answer. A goal is most helpful when it describes the outcome the user wants rather than restating the question. For example, with `ask=how do I create an API token`, a goal like `automate deployments from our CI pipeline` lets GitBook tailor the answer to that use case.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.
