[![Build Status](](
[![Inch CI](](

# JSON-API Query Builder

Build Ecto queries from JSON-API requests.

Docs can be found at [](

## Installation

The package can be installed by adding `json_api_query_builder` to your list of dependencies in `mix.exs`:

def deps do
  [{:json_api_query_builder, "~> 1.0"}]

## Features

JSON-API Query Builder can be used to construct an efficient Ecto query to handle the following kinds of requests, in arbitrary combinations.

### Sparse Fieldsets

Get all articles, including only the `title` and `description` fields


### Sorting

Get all articles, sorted by `category` ascending and `published` descending


### Included Resources

Get all articles, including related author, comments and comments user


### Attribute Filters

Get all articles with the animals `tag`


### Filter by related resource

Get all users who have an article with the animals `tag`


### Filter included resources

Get all users, including related articles that have the animals `tag`


### Pagination


## Usage

For each Ecto schema, create a related query builder module:

defmodule Article do
  use Ecto.Schema

  schema "articles" do
    field :body, :string
    field :description, :string
    field :slug, :string
    field :tag_list, {:array, :string}
    field :title, :string
    belongs_to :author, User, foreign_key: :user_id
    has_many :comments, Comment

  defmodule Query do
    use JsonApiQueryBuilder,
      schema: Article,
      type: "article",
      relationships: ["author", "comments"]

    @impl JsonApiQueryBuilder
    def filter(query, "tag", value), do: from(a in query, where: ^value in a.tag_list)
    def filter(query, "comments", params) do
      comment_query = from(Comment, select: [:article_id], distinct: true) |> Comment.Query.filter(params)
      from a in query, join: c in ^subquery(comment_query), on: == c.article_id
    def filter(query, "author", params) do
      user_query = from(User, select: [:id]) |> User.Query.filter(params)
      from a in query, join: u in ^subquery(user_query), on: a.user_id ==

    @impl JsonApiQueryBuilder
    def include(query, "comments", comment_params) do
      from query, preload: [comments: ^]
    def include(query, "author", author_params) do
      from query, select_merge: [:author_id], preload: [author: ^]

Then in an API request handler, use the query builder:

defmodule ArticleController do
  use MyAppWeb, :controller

  def index(conn, params) do
    articles =
      |> MyApp.Repo.all()

    # pass data and opts as expected by `ja_serializer`
    render("index.json-api", data: articles, opts: [
      fields: params["fields"],
      include: params["include"]

## Generated Queries

Using `join:` queries for filtering based on relationships, `preload:` queries for included resources and `select:` lists for sparse fieldsets, the generated queries are as efficient as what you would write by hand.

Eg the following index request:

params = %{
  "fields" => %{
    "article" => "description",
    "comment" => "body",
    "user" => "email,username"
  "filter" => %{
    "articles.tag" => "animals"
  "include" => "articles,articles.comments,articles.comments.user"

Produces one join query for filtering, and 3 preload queries

[debug] QUERY OK source="users" db=3.8ms decode=0.1ms queue=0.1ms
SELECT u0."email", u0."username", u0."id"
FROM "users" AS u0
  SELECT DISTINCT a0."user_id" AS "user_id"
  FROM "articles" AS a0
  WHERE ($1 = ANY(a0."tag_list"))
) AS s1
ON u0."id" = s1."user_id" ["animals"]

[debug] QUERY OK source="articles" db=1.9ms
SELECT a0."description", a0."id", a0."user_id"
FROM "articles" AS a0
WHERE (a0."user_id" = ANY($1))
ORDER BY a0."user_id" [[2, 1]]

[debug] QUERY OK source="comments" db=1.7ms
SELECT c0."body", c0."id", c0."user_id", c0."article_id"
FROM "comments" AS c0
WHERE (c0."article_id" = ANY($1))
ORDER BY c0."article_id" [[4, 3, 2, 1]]

[debug] QUERY OK source="users" db=1.3ms
SELECT u0."email", u0."username", u0."id", u0."id"
FROM "users" AS u0
WHERE (u0."id" = $1) [2]

## License


## Contributing

GitHub issues and pull requests welcome.