QueryObjectDefinitionInstancesQuery

QueryObjectDefinitionInstancesQuery returns rows from BPM.ObjectDefinitionInstance for one Object Definition and filters the values stored in each row's JSON Context.

Use this command when many entities have instances of the same definition, such as CREDIT_ASSESSMENT, and you need to find matching rows across those entities. Unlike DoSqlQuery, this command does not accept raw SQL. Definition codes, JSON paths, values, sorting, and pagination are validated or parameterized.

Example: approved credit assessments

var result = doCmd("QueryObjectDefinitionInstancesQuery", {
  definitionCode: "CREDIT_ASSESSMENT",
  filters: [
    {
      path: "decision.status",
      operator: "equals",
      dataType: "string",
      value: "APPROVED"
    }
  ],
  pageIndex: 0,
  pageSize: 20
});

Equivalent API request:

{
  "cmd": "QueryObjectDefinitionInstancesQuery",
  "data": "{\"definitionCode\":\"CREDIT_ASSESSMENT\",\"filters\":[{\"path\":\"decision.status\",\"operator\":\"equals\",\"dataType\":\"string\",\"value\":\"APPROVED\"}],\"pageIndex\":0,\"pageSize\":20}"
}

The command first resolves CREDIT_ASSESSMENT through BPM.ObjectDefinition.Code. It then queries only instances whose ObjectDefinitionId matches the resolved definition. A code is therefore not treated as a JSON field and cannot accidentally include instances from another definition.

The instances do not have to contain every property. A row with a missing property does not match an ordinary comparison on that path. isNull is the exception: it matches both a missing property and an explicit JSON null. Rows whose complete Context value is not valid JSON are excluded safely.

Request fields

Field	Type	Required	Default	Description
definitionCode	string	Yes	-	Exact ObjectDefinition.Code, for example CREDIT_ASSESSMENT
filters	array	No	[]	Up to 20 JSON-context filters
select	array	No	[]	Up to 20 scalar JSON fields to return. When omitted, the command returns the full instance row and context
filterLogic	string	No	all	all joins filters with AND; any joins them with OR
entityId	long	No	-	Restricts results to one associated entity ID
trackerId	string	No	-	Restricts results to one tracker ID
includeInactive	boolean	No	false	Includes inactive Object Definition instances
pageIndex	integer	No	0	Zero-based page index
pageSize	integer	No	20	Page size from 1 to 200
sortBy	string	No	createdAt	id, entityId, trackerId, createdAt, or updateAt
sortDirection	string	No	desc	asc or desc
timeoutSeconds	integer	No	provider default	Query timeout from 1 to 300 seconds

Each item in select has this shape:

Field	Type	Required	Default	Description
path	string	Yes	-	Scalar JSON path such as score, decision.status, or scores[0].value
alias	string	No	Last path segment	Property name used inside the returned fields object
dataType	string	No	string	string, integer, decimal, boolean, or datetime

When alias is omitted, a top-level path uses the complete property name and a nested path uses the final segment after the last dot. For example, score becomes score, while decision.status and $.decision.status both become status. Explicit and derived aliases must be unique, ignoring letter case.

string is the safest default because SQL Server's JSON_VALUE returns scalar JSON values as text. Use a typed value when the client needs a JSON number, boolean, or date-compatible value. Selecting an object or array produces null; projection is intentionally limited to scalar paths.

If a selected path is missing, explicitly null, an object/array, or cannot be converted to its requested type, that field is returned as null. Projection does not remove the row; use a filter when the row itself should be excluded.

Each item in filters has this shape:

Field	Type	Required	Description
path	string	Yes	Scalar JSON path such as score, decision.status, scores[0].value, or $.score
operator	string	Yes	Comparison operator listed below
dataType	string	No	string (default), integer, decimal, boolean, or datetime
value	scalar/array	Usually	Scalar value; array for in; two-item array for between; omitted for null checks

Supported operators:

Operator	Valid types	Value
equals, notEquals	all	One scalar
greaterThan, greaterThanOrEqual	all typed values	One scalar
lessThan, lessThanOrEqual	all typed values	One scalar
contains, startsWith, endsWith	string	One string
in	all	Non-empty array, maximum 100 values
between	all	Array containing exactly two values
isNull, isNotNull	all	No value

Return only selected fields

This request filters approved assessments and returns only score, decision.status, and riskGrade from each matching Context. The status alias is derived automatically from the final segment of decision.status, and all fields whose dataType is omitted default to string.

{
  "definitionCode": "CREDIT_ASSESSMENT",
  "select": [
    {
      "path": "score",
      "dataType": "integer"
    },
    {
      "path": "decision.status"
    },
    {
      "path": "riskGrade"
    }
  ],
  "filters": [
    {
      "path": "decision.status",
      "operator": "equals",
      "value": "APPROVED"
    }
  ],
  "pageIndex": 0,
  "pageSize": 20
}

Projected responses contain stable instance identifiers plus a fields object. They do not return the complete context:

{
  "isSuccessful": true,
  "message": "1 ObjectDefinitionInstance(s) matched.",
  "data": [
    {
      "id": 902,
      "entityId": 159,
      "trackerId": null,
      "fields": {
        "score": 720,
        "status": "APPROVED",
        "riskGrade": "LOW"
      }
    }
  ],
  "statusCode": "00",
  "totalRows": 1,
  "totalPages": 1,
  "size": 20,
  "count": 1,
  "pages": 1,
  "hasPrevious": false,
  "hasNext": false
}

To avoid an ambiguous response, the command rejects duplicate aliases. For example, selecting both decision.status and verification.status without assigning distinct aliases returns status code 400.

Multiple typed filters

This example returns active assessments with a score from 650 through 800 and a completed assessment date in 2026:

{
  "definitionCode": "CREDIT_ASSESSMENT",
  "filterLogic": "all",
  "filters": [
    {
      "path": "score",
      "operator": "between",
      "dataType": "integer",
      "value": [650, 800]
    },
    {
      "path": "assessment.completedAt",
      "operator": "between",
      "dataType": "datetime",
      "value": [
        "2026-01-01T00:00:00Z",
        "2026-12-31T23:59:59Z"
      ]
    },
    {
      "path": "decision.status",
      "operator": "in",
      "dataType": "string",
      "value": ["APPROVED", "REFERRED"]
    }
  ],
  "sortBy": "createdAt",
  "sortDirection": "desc",
  "pageIndex": 0,
  "pageSize": 50
}

Response

data is the current page of matching instances. count/totalRows is the number of matches before pagination, and pages/totalPages is the calculated page count. The following is the backward-compatible full-row response returned when select is omitted or empty.

{
  "isSuccessful": true,
  "message": "2 ObjectDefinitionInstance(s) matched.",
  "data": [
    {
      "id": 902,
      "objectDefinitionId": 46,
      "definitionCode": "CREDIT_ASSESSMENT",
      "definitionName": "Credit Assessment",
      "entityType": 310,
      "entityId": 159,
      "context": "{\"score\":720,\"decision\":{\"status\":\"APPROVED\"}}",
      "trackerId": null,
      "objectDefinitionVersion": 1,
      "notes": null,
      "isActive": true,
      "createdByUserId": "system",
      "updatedByUserId": null,
      "createdAt": "2026-10-22T08:30:00Z",
      "updateAt": "2026-10-22T08:30:00Z"
    }
  ],
  "statusCode": "00",
  "totalRows": 2,
  "totalPages": 1,
  "size": 50,
  "count": 2,
  "pages": 1,
  "hasPrevious": false,
  "hasNext": false
}

context remains the stored JSON string, matching the existing Object Definition Instance commands. Parse it only when the caller needs to access the full object.

SQL Server 2019 support

The generated query uses features available in SQL Server 2019:

• ISJSON ignores rows containing invalid JSON.
• JSON_VALUE reads scalar properties.
• TRY_CONVERT applies the requested type without failing the whole query when one row contains an invalid value.
• OPENJSON implements parameterized in filters.
• OFFSET ... FETCH performs pagination.

The database compatibility level must be at least 130 for OPENJSON. SQL Server 2019's normal compatibility level is 150. A DBA can verify it with:

SELECT name, compatibility_level
FROM sys.databases
WHERE name = DB_NAME();

The implementation does not use SQL Server 2022 or SQL Server 2025 JSON features.

Security and permissions

The caller needs either dev_view_object_definitions or dev_manage_object_definitions. If the resolved Object Definition has its own Permission, the caller must also hold that permission. A JSON permission array uses any-match semantics, consistent with the other Object Definition queries.

JSON values and paths are sent as database parameters. Sort columns are selected from a fixed allowlist. Paths accept ordinary property names and numeric array indexes; quoted/special-character property syntax is deliberately rejected.

Performance guidance

This command is intended for cross-instance searching, but JSON extraction still costs more than filtering a normal indexed column. For definitions queried frequently at high volume:

1. Keep definitionCode and the most selective filters in every request.
2. Use paging and a bounded page size.
3. Add an index on BPM.ObjectDefinitionInstance(ObjectDefinitionId, IsActive, EntityId) if production query plans show scans.
4. For a heavily used JSON property, consider a persisted computed column based on JSON_VALUE(Context, '$.property') and index that column after reviewing tenant schemas and migrations.

Difference from existing commands

• GetObjectDefinitionInstancesByDefinitionQuery filters normal instance columns and returns instances for a definition.
• QueryObjectDefinitionInstancesQuery additionally filters scalar values inside Context across all matching rows.
• DoSqlQuery accepts arbitrary SQL and should not be used when this constrained command covers the requirement.