PostgreSQL has great support for objects stored as JSON. This is useful for those moments when you need to store data that could be variably structured, such as responses from other services’ APIs, or data that frequently travels together within your relational tables.

A common trade-off for mixing scalar column data types (like varchar or integer) with column data types that handle more-complicated objects (like JSON) is that ORMs or data mappers sometimes can’t introspect on them for you, which means it becomes much harder to query that data.

Using Ecto’s embedded_schema helps introspect on those known values, but it doesn’t really assist you with querying those fields in SQL. This is where I became extremely greatful for Ecto’s escape hatch: fragment().

Define the Struct or Map in Ecto

Let’s dive into some code as an example:

I have a Vehicle.Photo schema that has several versions of the photo:

• craigslist_ad
• facebook_ad
• facebookcarouselad
• extra_large
• extra_small
• large
• medium
• original
• small

We decided to store the versions’ URLs inside a map in the database, because we’re going to use a set of the URLs at the same time inside of an HTML <img srcset />. You can read more about srcset from MDN and how it helps with responsive images.

The Ecto migration looks like this:

def up do
  alter table(:vehicle_photos) do
    add :standard_urls, :map
    add :facebook_urls, :map
    add :craigslist_urls, :map
  end
end

The Ecto schema looks like this:

schema "vehicle_photos" do
  field(:file, PhotoUploader.Type)

  embeds_one :standard_urls, StandardUrls, on_replace: :update do
    field(:extra_large, :string)
    field(:extra_small, :string)
    field(:large, :string)
    field(:medium, :string)
    field(:original, :string)
    field(:small, :string)
  end

  embeds_one :facebook_urls, FacebookUrls, on_replace: :update do
    field(:hero_ad, :string)
    field(:carousel_ad, :string)
  end

  embeds_one :craigslist_urls, CraigslistUrls, on_replace: :update do
    field(:ad, :string)
  end
end

Since this is a known structure, Ecto can introspect on the JSON values and cast and dump them to the appropriate Elixir data types, which is immensely helpful. Here I am achieving that by using embeds_one and specifying the struct. Once pulled from the database, Ecto will decode them.

Other times, you may not be able to do this ahead of time, so the schema might look like this (the api_response field):

schema "vehicle_photos" do
  field(:file, PhotoUploader.Type)
  field(:api_response, :map)
end

Query the JSON

Continuing with the struct example schema, we found out that some of our URLs weren’t being populated like we expected, so I had to find those photos and fix them. How do I query for them since they’re stored in PostgreSQL as JSON? We need to drop down into raw SQL:

def where_photo_urls_have_a_null(query) do
  query
  |> where([_q], fragment(
    """
    (facebook_urls IS NULL) OR
    (facebook_urls->>'ad_version' IS NULL) OR
    (facebook_urls->>'hero_version' IS NULL) OR
    (craigslist_urls->>'ad' IS NULL)
    """
  ))
end

The SQL operator ->> will leverage PostgreSQL’s JSON functions to retrieve the text or integers that are stored in the JSON. You can access them using this syntax: column->>key. In my case, I needed to find if the column was null, or it wasn’t null, then to ask if the JSON object has any keys that are null. This will work regardless of whether you use an embedded struct or a map, because PostgreSQL sees it as the same thing: JSON.

Here’s an example that checks for substrings:

def where_photo_url_wrong(query) do
  query
  |> where([_q], fragment(
    """
    (facebook_urls->>'hero_ad' NOT ILIKE ?) OR
    (facebook_urls->>'carousel_ad' NOT ILIKE ?) OR
    (craigslist_urls->>'ad' NOT ILIKE ?)
    """,
    "%facebook_hero_ad%",
    "%facebook_carousel_ad%",
    "%craigslist_ad%"
  ))
end

Make the Query Composable

Above is all I needed for my use case, but I wondered how I could continue querying those fields in a reusable way. For example, how do I chain these together in an OR statement that uses both of these fragments?

To do that, I’ll need to extract the fragment expressions and put them into a macro so they can be used within Ecto’s functions.

defmodule MyProject.SampleQuery.Fragments do
  import Ecto.Query.API, only: [fragment: 1]

  defmacro photo_urls_have_a_null do
    quote do
      fragment(
        """
        (facebook_urls IS NULL) OR
        (facebook_urls->>'ad_version' IS NULL) OR
        (facebook_urls->>'hero_version' IS NULL) OR
        (craigslist_urls->>'ad' IS NULL)
        """
      )
    end
  end

  defmacro photo_urls_not_contain([hero_ad_value, carousel_ad_value, ad_value]) do
    quote do
      fragment(
        """
        (facebook_urls->>'hero_ad' NOT ILIKE ?) OR
        (facebook_urls->>'carousel_ad' NOT ILIKE ?) OR
        (craigslist_urls->>'ad' NOT ILIKE ?)
        """,
        ^"%#{unquote(hero_ad_value)}%",
        ^"%#{unquote(carousel_ad_value)}%",
        ^"%#{unquote(ad_value)}%"
      )
    end
  end
end

Now that those fragments are extracted, let’s use them:

import MyProject.SampleQuery.Fragments
alias MyProject.Photo

defmodule MyProject.SampleQuery do
  def find_bad_photos(query \\ Photo) do
    query
    |> where([_p], photo_urls_have_a_null())
    |> or_where([_p], photo_urls_not_contain([
      "facebook_hero_ad",
      "facebook_carousel_ad",
      "craigslist_ad"
    ]))
    |> Repo.all
  end
end

Beautiful.

If you’d like to check out the code a little more, you can see this sample Ecto and Phoenix repo with tests.

This article only explains how to query a JSON object in the database and how it works with Ecto querying. If you’re needing to store an array of maps or structs, then check out Jon’s post Why Ecto’s Way of Storing Embedded Lists of Maps Makes Querying Hard.