odata-query
OData v4 query builder that uses a simple object-based syntax similar to MongoDB and js-data
Install
yarn add odata-query
or
npm install --save odata-query
and then use the library
import buildQuery from 'odata-query'
const query = buildQuery({...})
fetch(`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
buildQuery({ filter: {...} })
=> '?$filter=...'
Simple equality filter
const filter = { PropName: 1 };
buildQuery({ filter })
=> '?$filter=PropName eq 1'
Comparison operators
const filter = { PropName: { gt: 5 } };
buildQuery({ filter })
=> '?$filter=PropName gt 5'
Supported operators: eq
, ne
, gt
, ge
, lt
, le
, in
Logical operators
Implied and
with an array of objects
const filter = [{ SomeProp: 1 }, { AnotherProp: 2 }, 'startswith(Name, "foo")'];
buildQuery({ filter })
=> '?$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
const startDate = new Date(Date.UTC(2017, 0, 1))
const endDate = new Date(Date.UTC(2017, 2, 1))
const filter = { DateProp: { ge: startDate, le: endDate } }
buildQuery({ filter })
=> "?$filter=DateProp ge 2017-01-01T00:00:00Z and DateProp le 2017-03-01T00:00:00Z"
Explicit operator
const filter = {
and: [
{ SomeProp: 1 },
{ AnotherProp: 2 },
'startswith(Name, "foo")'
]
};
buildQuery({ filter })
=> '?$filter=SomeProp eq 1 and AnotherProp eq 2 and startswith(Name, "foo")'
Supported operators: and
, or
Collection operators
Implied and
Using an object
const filter = {
ItemsProp: {
any: {
SomeProp: 1,
AnotherProp: 2
}
}
};
buildQuery({ filter })
=> '?$filter=ItemsProp/any(i:i/SomeProp eq 1 and i/AnotherProp eq 2)'
or also as an array of object
const filter = {
ItemsProp: {
any: [
{ SomeProp: 1 },
{ AnotherProp: 2},
]
}
};
buildQuery({ filter })
=> '?$filter=ItemsProp/any(i:i/SomeProp eq 1 and i/AnotherProp eq 2)'
Explicit operator (and
, or
)
const filter = {
ItemsProp: {
any: {
or: [
{ SomeProp: 1 },
{ AnotherProp: 2},
]
}
}
};
buildQuery({ filter })
=> '?$filter=ItemsProp/any(i:(i/SomeProp eq 1 or i/AnotherProp eq 2)'
Supported operators: any
, all
Functions
String functions returning boolean
const filter = { PropName: { contains: 'foo' } };
buildQuery({ filter })
=> "$filter=contains(PropName,'foo')"
Supported operators: startswith
, endswith
, contains
Functions returning non-boolean values (string, int)
const filter = { 'length(PropName)': { gt: 10 } };
buildQuery({ filter })
=> "$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
const filter = { "indexof(PropName, 'foo')": { eq: 3 } };
buildQuery({ filter })
=> "$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.
import f from 'odata-filter-builder';
const filter = f().eq('TypeId', '1')
.contains(x => x.toLower('Name'), 'a')
.toString();
buildQuery({ filter })
Data types
GUID:
const filter = { "someProp": { eq: { type: 'guid', value: 'cd5977c2-4a64-42de-b2fc-7fe4707c65cd' } } };
buildQuery({ filter })
=> "?$filter=someProp eq cd5977c2-4a64-42de-b2fc-7fe4707c65cd"
Other types coming soon
Search
const search = 'blue OR green';
buildQuery({ search });
=> '?$search=blue OR green';
Selecting
const select = ['Foo', 'Bar'];
buildQuery({ select })
=> '?$select=Foo,Bar'
Ordering
const orderBy = ['Foo desc', 'Bar'];
buildQuery({ orderBy })
=> '?$orderby=Foo desc,Bar'
Expanding
Nested expand using slash seperator
const expand = 'Friends/Photos'
buildQuery({ expand })
=> '?$expand=Friends($expand=Photos)';
Nested expand with an object
const expand = { Friends: { expand: 'Photos' } }
buildQuery({ expand })
=> '?$expand=Friends($expand=Photos)';
Multiple expands as an array
Supports both string (with slash seperators) and objects
const expand = ['Foo', 'Baz'];
buildQuery({ expand })
=> '?$expand=Foo,Bar';
Filter expanded items
const expand = { Trips: { filter: { Name: 'Trip in US' } } };
buildQuery({ expand })
=> "?$expand=Trips($filter=Name eq 'Trip in US')";
Select only specific properties of expanded items
const expand = { Friends: { select: ['Name', 'Age'] } };
buildQuery({ expand })
=> '?$expand=Friends($select=Name,Age)';
Return only a subset of expanded items
const expand = { Friends: { top: 10 } };
buildQuery({ expand })
=> '?$expand=Friends($top=10)';
Order expanded items
const expand = { Products: { orderBy: 'ReleaseDate asc' } };
buildQuery({ expand })
=> "?$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
const expand = {
Friends: {
select: ['FirstName', 'LastName'],
top: 10,
filter: {
FirstName: { startswith: 'R' }
},
orderBy: 'LastName asc'
}
};
buildQuery({ expand })
=> '?$expand=Friends($select=Name,Age;$top=10;$filter=startswith eq 'R'))';
Pagination (skip and top)
Get page 3 (25 records per page)
const page = 3;
const perPage = 25;
const top = perPage;
const skip = perPage * (page - 1);
buildQuery({ top, skip })
=> '?$top=25&$skip=50'
Single-item (key)
Simple value
const key = 1;
buildQuery({ key })
=> '(1)'
As object (explicit key property
const key = { Id: 1 };
buildQuery({ key })
=> '(Id=1)'
Counting
Include count inline with result
const count = true;
const filter = { PropName: 1}
buildQuery({ count, filter })
=> '?$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)
const count = { PropName: 1 }
const query = buildQuery({ count })
=> '/$count?$filter=PropName eq 1'
Actions
Action on an entity
const key = 1;
const action = 'Test';
buildQuery({ key, action })
=> '(1)/Test'
Action on a collection
const action = 'Test';
buildQuery({ action })
=> '/Test'
Action parameters are passed in the body of the request.
Functions
Function on an entity
const key = 1;
const func = 'Test';
buildQuery({ key, func })
=> '(1)/Test'
Function on an entity with parameters
const key = 1;
const func = { Test: { One: 1, Two: 2 } };
buildQuery({ key, func })
=> '(1)/Test(One=1,Two=2)'
Function on a collection
const func = 'Test';
buildQuery({ func })
=> '/Test'
Function on a collection with parameters
const func = { Test: { One: 1, Two: 2 } };
buildQuery({ func })
=> '/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
const transform = {
aggregate: {
Amount: {
with: 'sum',
as: 'Total'
}
}
};
buildQuery({ transform });
=> '?$apply=aggregate(Amount with sum as Total)';
Supported aggregations: sum
, min
, max
, average
, countdistinct
Group by (simple)
const transform = [{
groupBy: {
properties: ['SomeProp'],
}
}]
buildQuery({ transform });
=> '?$apply=groupby((SomeProp))';
Group by with aggregation
const transform = {
groupBy: {
properties: ['SomeProp'],
transform: {
aggregate: {
Id: {
with: 'countdistinct',
as: 'Total'
}
}
}
}
}
buildQuery({ transform });
=> '?$apply=groupby((SomeProp),aggregate(Id with countdistinct as Total))';
Group by with filtering before and after
const transform = [{
filter: {
PropName: 1
}
},{
groupBy: {
properties: ['SomeProp'],
transform: [{
aggregate: {
Id: {
with: 'countdistinct',
as: 'Total'
}
}
}]
}
},{
filter: {
Total: { ge: 5 }
}
}]
buildQuery({ transform });
=> '?$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