Query Embedded Jsonb in Ash

Here's how to query fields in embedded jsonb in the Ash framework.

Jsonb is cool

Jsonb columns in Postgres are so cool. Dump your semi-structured data there as json. Query it from sql:

select json->>'jsonField' from my_table;

Flexible!

Ash Resources with jsonb

You can specify a jsonb attribute on an Ash resource:

defmodule MyLibrary.Shelves.Book do
  use Ash.Resource,
    domain: MyLibrary.Shelves,
    data_layer: AshPostgres.DataLayer,
    extensions: [AshGraphql.Resource]

  postgres do
    table "book"
    repo MyLibrary.Repo
  end

  attributes do
    # ...

    attribute :json, :map do
      public? true
    end
  end
end

Now I can access the stringified json. If I am using ash_graphql, I could query:

query fetchBooks {
  listBooks {
    results {
      json
    }
  }
}

And get back stringified json, like:

{ 
  "data": {
    "listBooks": {
      "results": [
         {
           "json": "{\"length\":33333,\"genres\":[\"Comedy\",\"Romance\"]
         }
       ]
    }
  }
}

It's a string. We can do better, but we need a struct

Ash Resources embedded types

Ash supports embedded resources via jsonb. Let's define the a BookMeta type for this json:

defmodule MyLibrary.Shelves.Book.BookMeta do
  use Ash.Resource,
    domain: MyLibrary.Shelves,
    data_layer: :embedded,
    extensions: [AshGraphql.Resource]

  attributes do
    attribute :length, :integer, allow_nil?: true, public?: true
    attribute :genres, {:array, :string},
      allow_nil?: true,
      public?: true
  end

  actions do
    create :create do
      accept [
        :length,
        :genres
      ]

      primary? true
    end
  end

  graphql do
    type :book_meta
  end
end

Notable here are that now all the fields must be specified. This makes it less flexible. If you try to query a book with json that has fields different than the declared attributes, the query will raise an error.

Note the data_layer: :embedded. This indicates an embedded type. There is no backing table in postgres.

The create action must be made to allow book table insertion with those accepted fields in the json field.

Then finally, add the graphql book_meta type so that it'll be defined in our graph schema.

Once we have this BookMeta resource, we can pop back to the Book definition and change the json attribute to use this type:

attribute :json, MyLibrary.Shelves.Book.BookMeta do
  public? true
end

This allows us to adjust our graphql query. json now has fields!:

query fetchBooks {
  listBooks {
    results {
      json {
        length
        genres
      }
    }
  }
}

And the resultant json is parsed and queryable down to the field level:

{ 
  "data": {
    "listBooks": {
      "results": [
         {
           "json": {
             "length": 33333,
             "genres": ["Comedy", "Romance"] 
            }
         }
       ]
    }
  }
}

This is so cool.

Flatten with calculations

We can make it a little cooler, more convenient and flatter with some Ash.Resource calculations. We'll add these to Book. This'll allow us to query the field directly as if it existed on Book, without the json intermediate container.

Let's make a calculation module:

defmodule MyLibrary.Shelves.Book.JsonFieldCalculation do
  use Ash.Resource.Calculation

  @impl true
  def init(opts) do
    if opts[:key] && is_atom(opts[:key]) do
      {:ok, opts}
    else
      {:error, "Expected a `key` option"}
    end
  end

  @impl true
  def load(_query, _opts, _context) do
    [:json]
  end

  @impl true
  def calculate(records, opts, _context) do
    Enum.map(records, fn book ->
      Map.get(book.json, opts[:key])
    end)
  end
end

Now we can use this calculation on Book:

defmodule MyLibrary.Shelves.Book do
  alias MyLibrary.Shelves.Book.JsonFieldCalculation

  calculations do
      calculate :length,
              :integer,
              {JsonFieldCalculation, key: :length} do
      public? true
    end
    calculate :genre,
              {:array, :string},
              {JsonFieldCalculation, key: :genres} do
      public? true
    end
  end
end

Now our query got even flatter:

query fetchBooks {
  listBooks {
    results {
      length
      genres
    }
  }
}

And the data did too:

{ 
  "data": {
    "listBooks": {
      "results": [
         {
           "length": 33333,
           "genres": ["Comedy", "Romance"] 
         }
       ]
    }
  }
}

Isn't that neat! Semi-structured data in the db. No new columns. Definition about the embedded resource in the app. Direct field-level querying on the api.