Learning › ECSQL Row Format
Edit this page

Format of a row returned from an ECSQL SELECT query

Code examples showing off the use of different row formats can be found in ECSQL Code Examples

When using Concurrent query a row format can be selected. By default ConcurrentQuery engine always render top level row as array of values and not object. But it also include meta data allowing array to be converted into two other formats. The format is controlled by flag QueryRowFormat which can have one of following values.

  • UseECSqlPropertyNames: Each row is an object in which each non-null column value can be accessed by its name as defined in the ECSql. Null values are omitted.
  • UseJsPropertyNames: Each row is an array of values accessed by an index corresponding to the property's position in the ECSql SELECT statement. Null values are included if they are followed by a non-null column, but trailing null values at the end of the array are omitted.
  • UseECSqlPropertyIndexes: The default format if none is specified by the caller

There is tiny overhead when accessing row as UseECSqlPropertyNames or UseJsPropertyNames as it require convert array values into a object with property name and values. We recommend using array values as is allowing much better performance.

Note that ECSqlStatement.getRow() function does not take row format as parameter and always return UseJsPropertyNames. In future the parameter will be added but we recommend using concurrent query on both frontend and backend as it is more efficient in term of memory and performance.

Property names

If the ECSQL select clause item

  • has a column alias, the alias, with the first character lowered, becomes the property name.
  • has no alias and is no ECSQL system property, the ECSQL select clause item, with the first character lowered, becomes the property name.
  • is an ECSQL system property (see also enum ECSqlSystemProperty):

System properties when UseJsPropertyNames options is used

System property JavaScript Type
ECInstanceId id
ECClassId className
SourceECInstanceId sourceId
SourceECClassId sourceClassName
TargetECInstanceId targetId
TargetECClassId targetClassName
Navigation property member JavaScript Type
Id id
RelClassId relClassName

System properties when UseECSqlPropertyNames options is used

Note: the property case will be same as specified in ECSQL

System property JavaScript Type
ECInstanceId ECInstanceId
ECClassId ECClassId
SourceECInstanceId SourceECInstanceId
SourceECClassId SourceECClassId
TargetECInstanceId TargetECInstanceId
TargetECClassId TargetECClassId
Navigation property member JavaScript Type
Id Id
RelClassId RelECClassId
Point property member JavaScript Type
X x
Y y
Z z

Property value types

The resulting types of the returned property values are these:

ECSQL type Extended Type JavaScript Typ
Boolean - boolean
Blob - Uint8Array
Blob BeGuid GuidString
ClassId system properties - fully qualified class name
Double - number
DateTime - ISO 8601 date time string
Id system properties - Id64String
Integer - number
Int64 - number
Int64 Id hexadecimal string
Point2d - XAndY
Point3d - XYAndZ
String - string
Navigation n/a NavigationValue
Struct n/a JavaScript object with properties of the types in this table
Array n/a array of the types in this table

Examples

ECSQL Row
SELECT ECInstanceId,ECClassId,Parent,LastMod,FederationGuid,UserLabel FROM bis.Element {id:"0x132", className:"generic.PhysicalObject", parent:{id:"0x444", relClassName:"bis.ElementOwnsChildElements"},lastMod:"2018-02-27T14:12:55.000Z",federationGuid:"274e25dc-8407-11e7-bb31-be2e44b06b34",userLabel:"My element"}
SELECT s.ECInstanceId schemaId, c.ECInstanceId classId FROM meta.ECSchemaDef s JOIN meta.ECClassDef c ON s.ECInstanceId=c.Schema.Id {schemaId:"0x132", classId:"0x332"}
SELECT count(*) FROM bis.Element {"count(*)": 31241}
SELECT count(*) cnt FROM bis.Element {cnt: 31241}

Last Updated: 12 February, 2024