ECSQL API Endpoint/Documentation

[liekubs]

Hi does anyone know the endpoint for performing ECSQL? or any documentation regarding it?..
I'd like to extract Openplant data on our iModel.
sample query.
Select * From ProcessFunctional.PIPING_NETWORK_SYSTEM

thanks,

[GytisCepk]

Currently there is no REST endpoint to do this, but you can use iTwin.js library to achieve this.

ECSQL queries can be run using IModelConnection.createQueryReader from @itwin/core-frontend package (code examples). Please note, that if you're not yet using iTwin.js library, you'll need to make sure that IModelApp is initialized with a valid authorization client.

[liekubs]

Thank you @GytisCepk this lead is enough for me to work through..

[mbuttery-ghd]

There is now a REST endpoint - POST https://api.bentley.com/imodel-query/itwins/{itwin}/imodels/{imodel}/changesets/{changeset}/queries
Refer documentation https://developer.bentley.com/apis/imodel-query/operations/run-query/

I have been able to use basic queries, however I have found that it does not like JSON strings in results

[GytisCepk]

I have found that it does not like JSON strings in results

@mbuttery-ghd, could provide more details on the issue you encountered ?

[mbuttery-ghd]

Here is my request body for a Revit model where I have used the material comments (string type) to add some data in JSON format

{
    "query": "SELECT DISTINCT e.ECInstanceId, e.ECClassId, e.UserLabel, mat.MATERIAL_NAME Material, mat.ALL_MODEL_INSTANCE_COMMENTS Comments, e.$ -> HOST_VOLUME_COMPUTED Volume FROM BisCore.Element e JOIN RevitDynamic.MaterialElem mat ON e.$ -> STRUCTURAL_MATERIAL_PARAM = mat.MATERIAL_NAME WHERE mat.PHY_MATERIAL_PARAM_TYPE = 2 AND json_valid(mat.ALL_MODEL_INSTANCE_COMMENTS)"
}

Here is the response from postman using POST to the query endpoint [with id's removed]

{
    "id": "cf4903da-0e35-4503-84cd-cdbb5cb25625",
    "state": "Completed",
    "rows": [],
    "links": {
        "self": "https://api.bentley.com/imodel-query/itwins/<itwin_id>/imodels/<imodel_id>/changesets/<changeset_id>/queries/cf4903da-0e35-4503-84cd-cdbb5cb25625?skip=0&limit=1000"
    }
}

And here is the result in the iModel console - using the SQL query copied directly from the body - the 'copy link' function returns empty and csv provides the rows shown

[GytisCepk]

I failed to reproduce this with our iModels.

Would it be possible for you to invite me (gytis.cepkauskas@bentley.com) to your iTwin so I could try to reproduce the issue with your iModel ? You can also clone iModel to a new iTwin and add me to that.

[mbuttery-ghd]

Invite sent

[mbuttery-ghd]

@GytisCepk, the query appears to be working now - with string element double quotes escaped