Each version can define queries and its own source creation template. In this article, we will configure the queryDefinitions property, which has two main purposes:

  • It provides Dawiso with the query syntax used to retrieve data.
  • It defines what data is being retrieved.

The blank template looks like this:

"queryDefinitions": [
    // Mandatory fields for ingesting objects.
    {
        "queryKey": "",
        "definition": "",
        "format": "json_lite",
        "order": 1,
        "fields": [
            { "key": "", "isKey": true },
            { "key": "", "isParentKey": true },
            { "key": "", "isName": true },
            { "key": "" }
        ],
        "options": { }
    },
    // Mandatory fields for ingesting relations between objects.
    {
        "queryKey": "",
        "definition": "",
        "format": "json_lite",
        "order": 1,
        "fields": [
            { "key": "", "isFromKey": true },
            //{ "key": "", "isRelationTypeKey": true },
            { "key": "", "isToKey": true },
        ],
        "options": { }
    }
],
PropertyDescription
queryKeyKey of the query the definition belongs to.
definitionQuery syntax in JSON lite format. Each query represents one table.
formatjson_lite
orderOrder of the executed query. It’s important to ensure query definitions do not have the same order number as it may cause the ingestion to fail.
fieldsArray of fields that will be ingested (list of table columns). Every query definition must have fields with the following features: isKey: Identifies the field as the unique identifier of the object., isName: Identifies the field as the name of the object. Names are limited to 200 characters., isParentKey: Key of the object’s parent. For relations queries, the required fields are different. We will take a closer look at it later.
optionsOptions are one of the two ways which allow us to ingest relations.

Query Definitions Rules

Warning

The following rules are mandatory. Without them, ingestion may be incomplete or fail entirely.

  1. Order of queries, 2. Unique keys, 3. Parent objects specification, 4. Relations validation

1. Order of queries

The order in which you run your queries is crucial for successful ingestion. Follow this order:

Top-level objects → Child objects → Relations

  • Start from the top of the hierarchy. Always ingest parent objects before their children. If a child object is ingested before its parent exists, it will appear at the root level without proper hierarchy.
  • Relations come last. Relations reference existing objects, so all related objects must already be ingested. If relation queries run too early, they won’t be created.

Following this order ensures that hierarchy and connections are correctly established in Dawiso.

2. Unique keys

Each object in your table must have a unique identifier that will serve as the object key during ingestion. If your table uses simple numeric IDs (e.g. auto-incremented rows), modify them to be globally unique by appending the object type name. This ensures proper identification and avoids collisions across object types.

For example, in SQL:

SELECT CAST([RecipeID] AS VARCHAR(10)) + '_recipe' [object_key],CAST([CuisineID] AS VARCHAR(10)) + '_cuisine' [parent_object_key],[Name], [Description], [Difficulty], [RecipeOwner], [BestCook], [LastMade]
FROM [dbo].[Recipe]

Instead of IDs which could be 1 and  1 for both object ID and its parent ID, we will get:

  • 1_recipe
  • 1_cuisine

3. Parent objects specification

When ingesting objects with a parent-child structure, make sure all parent objects are also present in your data and have their object keys. For example, if a recipe belongs to a category, that parent must be defined and ingested as an object.

If a parent key doesn’t match any existing object key, the object will be placed at the root level of the hierarchy. In such cases, you will need to fix the query and re-ingest the data.

Just like object keys, if parent IDs are plain numbers, we recommend converting them into unique keys by appending the object type. This ensures consistency and correct linkage in Dawiso.

4. Relations validation

When defining a query for a relation type, make sure that both from_object_key and to_object_key exist in your data and that the objects have already been ingested. If a relation references objects that don’t exist, the relation type may not be recognized correctly, or the entire ingestion process can fail.

Query Definition: Objects

Before moving to the next property, let’s take a look at some query examples. In this example, we will be extracting the Cuisine table and all its columns.

The SQL query is the following.

SELECT
    CAST([CuisineID] AS VARCHAR(10)) + '_cuisine' [object_key],
    CAST([ParentID] AS VARCHAR(10)) + '_cuisine' [parent_object_key],
    [Name], [Description]
    FROM [dbo].[Cuisine]

Every query ingesting object types must have the following fields:

  • A field that will be used as the object key, identified by the isKey feature.
  • A field that will be used as the parent key, identified by the isParentKey feature.
  • A field that will be used as the object name, identified by the isName feature.
Warning

Object names are limited to a maximum of 200 characters. Make sure your data respects this limit to avoid ingestion errors.

In JSON lite, the query would be transformed to the following format, which is what we will use in the application package:

SELECT \r\n\tCAST([CuisineID] AS VARCHAR(10)) + '_cuisine' [object_key], \r\n\tCAST([ParentID] AS VARCHAR(10)) + '_cuisine'  [parent_object_key],\r\n\t[Name],\r\n\t[Description]\r\nFROM [dbo].[Cuisine]
Tip

Every table represents one object type with its attributes with each having its own query definition.

In the example package and SQL script file, you can find the rest of the queries used in the example package.

Click here to hide the example.The full query definition for our Cuisine object type will look like the following:

"queryDefinitions": [
    {
        "queryKey": "cuisine",
        "definition": "SELECT \r\n\tCAST([CuisineID] AS VARCHAR(10)) + '_cuisine' [object_key], \r\n\tCAST([ParentID] AS VARCHAR(10)) + '_cuisine' [parent_object_key],\r\n\t[Name],\r\n\t[Description]\r\nFROM [dbo].[Cuisine]",
        "format": "json_lite",
        "order": 1,
        "fields": [
            {
                "key": "object_key",
                "isKey": true
            },
            {
                "key": "parent_object_key",
                "isParentKey": true
            },
            {
                "key": "name",
                "isName": true
            },
            {
                "key": "Description"
            }
        ],
        "options": { }
    },
]
Tip

The fields property defines which columns were extracted and identifies which should be treated as the key, the parent key, and the name.

Make sure all fields are ingested in a format that’s compatible with Dawiso.

In our example, the Ingredient table includes a “Needs restocking” field stored as a BIT (0 or 1). Since Dawiso expects boolean fields to be represented as the strings ‘true’ or ‘false’, we used a CASE expression in the query to convert the values accordingly.

SELECT
    CAST([IngredientID] AS VARCHAR(10)) + '_ingredient' AS [object_key],
    CAST([IngredientCategoryID] AS VARCHAR(10)) + '_ingredient_category' AS [parent_object_key],
    [Name],
    [Description],
    CASE [InStock]
        WHEN 1 THEN 'true'
        ELSE 'false'
    END AS [InStock]
FROM [dbo].[Ingredient];

Query Definition: Relations

To ingest relations between objects, first, we need to make sure each relation type is defined in its own table.

For example, the table with the Recommendations relations will look like this:

CREATE TABLE [dbo].[RecipeRecommendation] (
    [RecipeRecommendationID] INT NOT NULL IDENTITY PRIMARY KEY,
    [RecipeID] INT NULL REFERENCES [dbo].[Recipe]([RecipeID]),
    [RecommendedRecipeID] INT NULL REFERENCES [dbo].[Recipe]([RecipeID]),
    CONSTRAINT [UC_4] UNIQUE ([RecipeID],[RecommendedRecipeID])
)
GO

Query definitions ingesting relations can be defined in two ways:

  1. All relations are ingested at once thanks to the relation type being defined as a separate field in the query itself. The field will then be identified using the isRelationTypeKey feature.
  2. Each relation type is ingested individually and the relation type will be defined using the options property.

Ingest All Relation Types in One Query

When ingesting all relations, you can have multiple select queries in one query definition.

Each of the queries ingesting relations must have the same schema with the following fields:

  • A field that will be used as the key of the object from which the relation originates, identified by the isFromKey feature.
  • A field that will be used as the key of the relation type, identified by the isRelationTypeKey feature.
  • A field that will be used as the key of the target object, identified by the isToKey feature.
SELECT CAST([DietaryCategoryID] AS VARCHAR(10)) + '_dietary_category' to_object_key,'recipe_scanner_requires' relation_type_key,CAST([IngredientID] AS VARCHAR(10)) + '_ingredient' from_object_key
FROM [dbo].[DietaryCategoryIngredient] [r]

UNION ALL

SELECT CAST([RecipeID] AS VARCHAR(10)) + '_recommending_recipe' to_object_key,'recipe_scanner_isRecommending' relation_type_key,CAST([RecommendedRecipeID] AS VARCHAR(10)) + '_recommended_recipe' from_object_key
FROM [dbo].[RecipeRecommendation] [r]

Click here to hide the example.In the example below, the query definition will ingest the following object relations:

  • Dietary Category requires Ingredient
  • Recipe is recommending Recipe
    {
“queryKey”: “relations_ingestion”,
“definition”: “SELECT \r\n\tCAST([DietaryCategoryID] AS VARCHAR(10)) + ‘_dietary_category’ to_object_key,\r\n\t’recipe_scanner_requires’ relation_type_key,\r\n\tCAST([IngredientID] AS VARCHAR(10)) + ‘_ingredient’ from_object_key\r\nFROM [dbo].[DietaryCategoryIngredient] [r] \r\n\r\nUNION ALL\r\n\r\nSELECT \r\n\tCAST([RecipeID] AS VARCHAR(10)) + ‘_recommending_recipe’ to_object_key,\r\n\t’recipe_scanner_isRecommending’ relation_type_key,\r\n\tCAST([RecommendedRecipeID] AS VARCHAR(10)) + ‘_recommended_recipe’ from_object_key\r\nFROM [dbo].[RecipeRecommendation] [r]”,
“format”: “json_lite”,
“order”: 7,
“fields”: [
{
“key”: “from_object_key”,
“isFromKey”: true
},
{
“key”: “relation_type_key”,
“isRelationTypeKey”: true
},
{
“key”: “to_object_key”,
“isToKey”: true
}
],
“options”: { }
}
Tip

Using the UNION ALL operator to create your table ensures that all queries follow the same schema structure.

For the full query definitions ingesting four relation types, including alternatives, see the example package and scripts.

Ingest a Relation Type per Query

Another way to ingest relations is to ingest a relation type per query and query definition. In this case, there is no need for an extra column specifying the relation type in the SELECT statement.

SELECT CAST([RecipeID] AS VARCHAR(10)) + '_recipe' to_object_key,CAST([DietaryCategoryID] AS VARCHAR(10)) + '_dietary_category' from_object_key
FROM [dbo].[DietaryCategoryRecipe] [r]

Instead of identifying a column as the relation type, we must assign the query definition as a relation type. This is done using the options property and the relation type key (in the packageKey_relationTypeKey format):

"options": {
    "relationTypeKey": "packageKey_relationTypeKey"
}

Click here to hide the example.In our example package, the has dietary category would be ingested in the following way:

{
    "queryKey": "has_dietary_category",
    "definition": "SELECT \r\n\tCAST([RecipeID] AS VARCHAR(10)) + '_recipe' to_object_key,\r\n\tCAST([DietaryCategoryID] AS VARCHAR(10)) + '_dietary_category' from_object_key\r\nFROM [dbo].[DietaryCategoryRecipe] [r]",
    "format": "json_lite",
    "order": 7,
    "fields": [
        {
            "key": "from_object_key",
            "isFromKey": true
        },
        {
            "key": "to_object_key",
            "isRelationTypeKey": true
        }
    ],
    "options": {
        "relationTypeKey": "recipe_scanner_has_dietary_category"
    }
},

Ingesting User Relations

User relations can't be ingested the way objects, relations, and attributes are (i.e., by defining a query and mapping the metadata to its Dawiso counterpart). However, there are workarounds to avoid assigning all users manually to roles like stewards, best cooks, and more:

A. Use MCP to Bulk Edit Roles

  1. Configure your AI tool to connect to the Dawiso Model Context Protocol (MCP) endpoint.

  2. Ask the AI tool to assign users by providing a list of objects and their roles (e.g., owners, stewards). Dawiso will then create the corresponding relations automatically.

  3. You can also pair it with automation rules to notify the users when they are assigned to a new object.B. Use Data Factory (DFA)

  4. Add a column for each role in your source database, with values set to email addresses.

  5. For each role, create a corresponding attribute type in your Dawiso application package.

  6. When the data is ingested, the Call Data Factory automation can process these attributes and assign user relations based on the ingested email values.