odata-query

OData v4 query builder that uses a simple object-based syntax similar to MongoDB and js-data

Install

npm install odata-query

and then use the library

1import buildQuery from 'odata-query'
2
3const query = buildQuery({...})
4fetch(`http://localhost${query}`)

where the query object syntax for {...} is defined below. There is also react-odata which utilizies this library for a declarative React component.

Usage

See tests for examples as well

• Filtering
  • Simple equality filter
  • Comparison operators
  • Logical operators
    • Implied and with an array of objects
    • Implied and with multiple comparison operators for a single property
    • Explicit operator
  • Collection operators - any, all
    • Implied and with an object or array of objects
    • Explicit operator (and, or, and not)
    • Implied all operators on collection item itself
  • Functions
    • String functions returning boolean
    • Functions returning non-boolean values (string, int)
    • Functions returning non-boolean values (string, int) with parameters
  • Strings
  • Data types
  • Search
• Selecting
• Ordering
• Expanding
  • Nested expand using slash seperator
  • Nested expand with an object
  • Multiple expands as an array
  • Filter expanded items
  • Select only specific properties of expanded items
  • Return only a subset of expanded items
  • Order expanded items
  • filter, select, top, and orderBy can be used together
• Pagination (skip and top)
• Single-item (key)
• Counting
• Actions
• Functions
• Transforms

Filtering

1buildQuery({ filter: {...} })
2=> '?$filter=...'

Simple equality filter

1const filter = { PropName: 1 };
2buildQuery({ filter })
3=> '?$filter=PropName eq 1'

Comparison operators

1const filter = { PropName: { gt: 5 } };
2buildQuery({ filter })
3=> '?$filter=PropName gt 5'

Supported operators: eq, ne, gt, ge, lt, le, in

Using the in operator is also similar to the previous example.

1const filter = { PropName: { in: [1, 2, 3] } };
2buildQuery({ filter })
3=> '?$filter=PropName in (1,2,3)'

Logical operators

Implied and with an array of objects

1const filter = [{ SomeProp: 1 }, { AnotherProp: 2 }, 'startswith(Name, "foo")'];
2buildQuery({ filter })
3=> '?$filter=SomeProp eq 1 and AnotherProp eq 2 and startswith(Name, "foo")'

Implied and with multiple comparison operators for a single property

Useful to perform a between query on a Date property

1const startDate = new Date(Date.UTC(2017, 0, 1))
2const endDate = new Date(Date.UTC(2017, 2, 1))
3const filter = { DateProp: { ge: startDate, le: endDate } }
4buildQuery({ filter })
5=> "?$filter=DateProp ge 2017-01-01T00:00:00Z and DateProp le 2017-03-01T00:00:00Z"

Explicit operator

1const filter = {
2  and: [
3    { SomeProp: 1 },
4    { AnotherProp: 2 },
5    'startswith(Name, "foo")'
6  ]
7};
8
9buildQuery({ filter })
10=> '?$filter=SomeProp eq 1 and AnotherProp eq 2 and startswith(Name, "foo")'

1const filter = {
2  not: {
3    and:[
4      {SomeProp: 1}, 
5      {AnotherProp: 2}
6    ]
7  }
8};
9
10buildQuery({ filter })
11=> '?$filter=(not (SomeProp eq 1) and (AnotherProp eq 2))'

Supported operators: and, or, and not.

Collection operators

Empty any

Using an empty object

1const filter = {
2  ItemsProp: {
3    any: {}
4  }
5};
6
7buildQuery({ filter })
8=> '?$filter=ItemsProp/any()'

or also as an empty array

1const filter = {
2  ItemsProp: {
3    any: []
4  }
5};
6
7buildQuery({ filter })
8=> '?$filter=ItemsProp/any()'

Implied and

Using an object

1const filter = {
2  ItemsProp: {
3    any: {
4      SomeProp: 1,
5      AnotherProp: 2
6    }
7  }
8};
9
10buildQuery({ filter })
11=> '?$filter=ItemsProp/any(i:i/SomeProp eq 1 and i/AnotherProp eq 2)'

or also as an array of object

1const filter = {
2  ItemsProp: {
3    any: [
4      { SomeProp: 1 },
5      { AnotherProp: 2},
6    ]
7  }
8};
9
10buildQuery({ filter })
11=> '?$filter=ItemsProp/any(i:i/SomeProp eq 1 and i/AnotherProp eq 2)'

Explicit operator (and, or, and not)

1const filter = {
2  ItemsProp: {
3    any: {
4      or: [
5        { SomeProp: 1 },
6        { AnotherProp: 2},
7      ]
8    }
9  }
10};
11
12buildQuery({ filter })
13=> '?$filter=ItemsProp/any(i:(i/SomeProp eq 1 or i/AnotherProp eq 2)'

1const filter = {
2  not: {
3    ItemsProp: {
4      any: {
5        or: [
6          { SomeProp: 1 },
7          { AnotherProp: 2},
8        ]
9      }
10    }
11  }
12};
13
14buildQuery({ filter })
15=> '?$filter=not ItemsProp/any(i:((i/SomeProp eq 1) or (i/AnotherProp eq 2)))'

Implied all operators on collection item itself

ITEM_ROOT is special constant to mark collection with primitive type

'in' operator

1const filter = {
2    tags: {
3      any: {
4        [ITEM_ROOT]: { in: ['tag1', 'tag2']},
5      },
6    },
7};
8
9buildQuery({ filter })
10=> "?$filter=tags/any(tags:tags in ('tag1','tag2'))"

'or' operator on collection item itself

1const filter = {
2    tags: {
3      any: {
4        or: [
5          { [ITEM_ROOT]: 'tag1'},
6          { [ITEM_ROOT]: 'tag2'},
7        ]
8      }
9    }
10};
11
12buildQuery({ filter })
13=> "?$filter=tags/any(tags:((tags eq 'tag1') or (tags eq 'tag2')))";

'and' operator on collection item itself and nested item

1 const filter = {
2    tags: {
3      any: [
4          { [ITEM_ROOT]: 'tag1'},
5          { [ITEM_ROOT]: 'tag2'},
6          { prop: 'tag3'},
7        ]
8    }
9};
10
11buildQuery({ filter });
12=> "?$filter=tags/any(tags:tags eq 'tag1' and tags eq 'tag2' and tags/prop eq 'tag3')";

Function on collection item itself

1const filter = {
2    tags: {
3      any: {
4        [`tolower(${ITEM_ROOT})`]: 'tag1'
5      }
6    }
7};
8
9buildQuery({ filter });
10=> "?$filter=tags/any(tags:tolower(tags) eq 'tag1')";

Supported operators: any, all

Functions

String functions returning boolean

1const filter = { PropName: { contains: 'foo' } };
2buildQuery({ filter })
3=> "$filter=contains(PropName,'foo')"

Supported operators: startswith, endswith, contains

Functions returning non-boolean values (string, int)

1const filter = { 'length(PropName)': { gt: 10 } };
2buildQuery({ filter })
3=> "$filter=length(PropName) gt 10"

Supported operators: length, tolower, toupper, trim, day, month, year, hour, minute, second, round, floor, ceiling

Functions returning non-boolean values (string, int) with parameters

1const filter = { "indexof(PropName, 'foo')": { eq: 3 } };
2buildQuery({ filter })
3=> "$filter=indexof(PropName, 'foo') eq 3"

Supported operators: indexof, substring

Strings

A string can also be passed as the value of the filter and it will be taken as is. This can be useful when using something like odata-filter-builder or if you want to just write the OData filter sytnax yourself but use the other benefits of the library, such as groupBy, expand, etc.

1import f from 'odata-filter-builder';
2
3const filter = f().eq('TypeId', '1')
4                  .contains(x => x.toLower('Name'), 'a')
5                  .toString();
6buildQuery({ filter })

Data types

GUID:

1const filter = { "someProp": { eq: { type: 'guid', value: 'cd5977c2-4a64-42de-b2fc-7fe4707c65cd' } } };
2buildQuery({ filter })
3=> "?$filter=someProp eq cd5977c2-4a64-42de-b2fc-7fe4707c65cd"

Duration:

1const filter = { "someProp": { eq: { type: 'duration', value: 'PT1H' } } };
2buildQuery({ filter })
3=> "?$filter=someProp eq duration'PT1H'"

Binary:

1const filter = { "someProp": { eq: { type: 'binary', value: 'YmluYXJ5RGF0YQ==' } } };
2buildQuery({ filter })
3=> "?$filter=someProp eq binary'YmluYXJ5RGF0YQ=='"

Decimal:

1const filter = { "someProp": { eq: { type: 'decimal', value: '12.3456789' } } };
2buildQuery({ filter })
3=> "?$filter=someProp eq 12.3456789M"

Raw:

1const filter = { "someProp": { eq: { type: 'raw', value: `datetime'${date.toISOString()}'` } } };
2buildQuery({ filter })
3=> "?$filter=someProp eq datetime'2021-07-08T12:27:08.122Z'"

• Provides full control over the serialization of the value. Useful to pass a data type.

Note that as per OData specification, binary data is transmitted as a base64 encoded string. Refer to Primitive Types in JSON Format, and binary representation.

Search

1const search = 'blue OR green';
2buildQuery({ search });
3=> '?$search=blue OR green';

Selecting

1const select = ['Foo', 'Bar'];
2buildQuery({ select })
3=> '?$select=Foo,Bar'

Ordering

1const orderBy = ['Foo desc', 'Bar'];
2buildQuery({ orderBy })
3=> '?$orderby=Foo desc,Bar'

Expanding

Nested expand using slash seperator

1const expand = 'Friends/Photos'
2buildQuery({ expand })
3=> '?$expand=Friends($expand=Photos)';

Nested expand with an object

1const expand = { Friends: { expand: 'Photos' } }
2buildQuery({ expand })
3=> '?$expand=Friends($expand=Photos)';

Multiple expands as an array

Supports both string (with slash seperators) and objects

1const expand = ['Foo', 'Baz'];
2buildQuery({ expand })
3=> '?$expand=Foo,Bar';

Filter expanded items

1const expand = { Trips: { filter: { Name: 'Trip in US' } } };
2buildQuery({ expand })
3=> "?$expand=Trips($filter=Name eq 'Trip in US')";

Select only specific properties of expanded items

1const expand = { Friends: { select: ['Name', 'Age'] } };
2buildQuery({ expand })
3=> '?$expand=Friends($select=Name,Age)';

Return only a subset of expanded items

1const expand = { Friends: { top: 10 } };
2buildQuery({ expand })
3=> '?$expand=Friends($top=10)';

Order expanded items

1const expand = { Products: { orderBy: 'ReleaseDate asc' } };
2buildQuery({ expand })
3=> "?$expand=Products($orderby=ReleaseDate asc)";

filter, select, top, and orderBy can be used together

Select only the first and last name of the top 10 friends who's first name starts with "R" and order by their last name

1const expand = {
2  Friends: {
3    select: ['FirstName', 'LastName'],
4    top: 10,
5    filter: {
6      FirstName: { startswith: 'R' }
7    },
8    orderBy: 'LastName asc'
9  }
10};
11buildQuery({ expand })
12=> '?$expand=Friends($select=Name,Age;$top=10;$filter=startswith eq 'R'))';

Pagination (skip and top)

Get page 3 (25 records per page)

1const page = 3;
2const perPage = 25;
3const top = perPage;
4const skip = perPage * (page - 1);
5buildQuery({ top, skip })
6=> '?$top=25&$skip=50'

Single-item (key)

Simple value

1const key = 1;
2buildQuery({ key })
3=> '(1)'

As object (explicit key property

1const key = { Id: 1 };
2buildQuery({ key })
3=> '(Id=1)'

Counting

Include count inline with result

1const count = true;
2const filter = { PropName: 1}
3buildQuery({ count, filter })
4=> '?$count=true&$filter=PropName eq 1'

Or you can return only the count by passing a filter object to count (or empty object to count all)

1const count = { PropName: 1 }
2const query = buildQuery({ count })
3=> '/$count?$filter=PropName eq 1'

Actions

Action on an entity

1const key = 1;
2const action = 'Test';
3buildQuery({ key, action })
4=> '(1)/Test'

Action on a collection

1const action = 'Test';
2buildQuery({ action })
3=> '/Test'

Action parameters are passed in the body of the request.

Functions

Function on an entity

1const key = 1;
2const func = 'Test';
3buildQuery({ key, func })
4=> '(1)/Test'

Function on an entity with parameters

1const key = 1;
2const func = { Test: { One: 1, Two: 2 } };
3buildQuery({ key, func })
4=> '(1)/Test(One=1,Two=2)'

Function on a collection

1const func = 'Test';
2buildQuery({ func })
3=> '/Test'

Function on a collection with parameters

1const func = { Test: { One: 1, Two: 2 } };
2buildQuery({ func })
3=> '/Test(One=1,Two=2)'

Transforms

Transforms can be passed as an object or an array (useful when applying the same transform more than once, such as filter)

Aggregations

1const transform = {
2  aggregate: {
3    Amount: {
4      with: 'sum',
5      as: 'Total'
6    }
7  }
8};
9buildQuery({ transform });
10=> '?$apply=aggregate(Amount with sum as Total)';

Supported aggregations: sum, min, max, average, countdistinct

Group by (simple)

1const transform = [{
2  groupBy: {
3    properties: ['SomeProp'],
4  }
5}]
6buildQuery({ transform });
7=> '?$apply=groupby((SomeProp))';

Group by with aggregation

1const transform = {
2  groupBy: {
3    properties: ['SomeProp'],
4    transform: {
5      aggregate: {
6        Id: {
7          with: 'countdistinct',
8          as: 'Total'
9        }
10      }
11    }
12  }
13}
14buildQuery({ transform });
15=> '?$apply=groupby((SomeProp),aggregate(Id with countdistinct as Total))';

Group by with filtering before and after

1const transform = [{
2  filter: {
3    PropName: 1
4  }
5},{
6  groupBy: {
7    properties: ['SomeProp'],
8    transform: [{
9      aggregate: {
10        Id: {
11          with: 'countdistinct',
12          as: 'Total'
13        }
14      }
15    }]
16  }
17},{
18  filter: {
19    Total: { ge: 5 }
20  }
21}]
22buildQuery({ transform });
23=> '?$apply=filter(PropName eq 1)/groupby((SomeProp),aggregate(Id with countdistinct as Total))/filter(Total ge 5)';

Supported transforms: aggregate, groupby, filter. Additional transforms may be added later

OData specs

• OData Version 4.0. Part 1: Protocol Plus Errata 03
• OData Version 4.0. Part 2: URL Conventions Plus Errata 03
• OData Extension for Data Aggregation Version 4.0