Selecto JSON Operations Workbook

Setup 1) Install Dependencies

Run this cell first. It installs Selecto and related packages from Hex for this Livebook session.

selecto_dep = {:selecto, "~> 0.3.5"}

Mix.install([
  selecto_dep,
  {:postgrex, "~> 0.17"},
  {:ecto_sql, "~> 3.11"},
  {:jason, "~> 1.4"},
  {:kino, "~> 0.12"}
])

IO.puts("Using Selecto dependency: #{inspect(selecto_dep)}")

Overview

This workbook focuses on JSON/JSONB querying patterns in Selecto.

1. Project JSON fields into relational columns (json_select)
2. Filter rows by JSON content (json_filter)
3. Order rows by JSON paths (json_order_by)
4. Mix JSON operations with standard relational filters and sorting

flowchart LR
  row[Product row]
  metadata[metadata jsonb]
  extract[Extract JSON paths]
  filter[JSON filter]
  order[JSON-aware ordering]
  out[Combined result]

  row --> metadata --> extract --> out
  metadata --> filter --> out
  metadata --> order --> out

graph TD
  m[metadata]
  pb[price_band]
  wh[warehouse]
  zone[zone]
  flags[flags]
  flag0[flags[0]]

  m --> pb
  m --> wh --> zone
  m --> flags --> flag0

Before running, initialize the sample database:

cd selecto_examples
mix setup

Setup 2) Connect to Repo and Build Product Domain

This cell defines a local Repo and a product domain that includes metadata (jsonb) and tags (array).

defmodule JsonLab.Repo do
  use Ecto.Repo,
    otp_app: :json_lab_livebook,
    adapter: Ecto.Adapters.Postgres
end

defmodule JsonLab.ProductDomain do
  @moduledoc false

  def domain do
    %{
      name: "Products",
      source: %{
        source_table: "products",
        primary_key: :id,
        fields: [
          :id,
          :name,
          :sku,
          :description,
          :price,
          :stock_quantity,
          :reorder_level,
          :active,
          :featured,
          :tags,
          :metadata,
          :category_id,
          :supplier_id,
          :inserted_at
        ],
        columns: %{
          id: %{type: :integer},
          name: %{type: :string},
          sku: %{type: :string},
          description: %{type: :text},
          price: %{type: :decimal},
          stock_quantity: %{type: :integer},
          reorder_level: %{type: :integer},
          active: %{type: :boolean},
          featured: %{type: :boolean},
          tags: %{type: {:array, :string}},
          metadata: %{type: :jsonb},
          category_id: %{type: :integer},
          supplier_id: %{type: :integer},
          inserted_at: %{type: :utc_datetime}
        },
        associations: %{}
      },
      schemas: %{},
      joins: %{},
      default_selected: ["name", "sku", "price", "metadata"],
      default_order_by: [{"name", :asc}]
    }
  end
end

repo_config = [
  database: System.get_env("SELECTO_EXAMPLES_DB", "selecto_examples_dev"),
  username: System.get_env("SELECTO_EXAMPLES_DB_USER", "postgres"),
  password: System.get_env("SELECTO_EXAMPLES_DB_PASS", "postgres"),
  hostname: System.get_env("SELECTO_EXAMPLES_DB_HOST", "localhost"),
  port: String.to_integer(System.get_env("SELECTO_EXAMPLES_DB_PORT", "5432")),
  pool_size: 5
]

case Process.whereis(JsonLab.Repo) do
  nil -> {:ok, _pid} = JsonLab.Repo.start_link(repo_config)
  _pid -> :ok
end

config = %{
  repo: JsonLab.Repo,
  product_domain: JsonLab.ProductDomain.domain()
}

{:ok, result} = Ecto.Adapters.SQL.query(config.repo, "select count(*) from products", [])
[[product_count]] = result.rows
IO.puts("Connected. Products in dataset: #{product_count}")

config

Setup 3) Seed Metadata for Demonstrations

This cell refreshes products.metadata with predictable JSON keys used in the examples.

seed_result =
  config.repo.query!("""
  UPDATE products
  SET metadata = jsonb_build_object(
    'price_band',
    CASE
      WHEN price >= 100 THEN 'premium'
      WHEN price >= 50 THEN 'mid'
      ELSE 'budget'
    END,
    'warehouse',
    jsonb_build_object(
      'zone',
      CASE WHEN stock_quantity <= reorder_level THEN 'restock' ELSE 'in_stock' END,
      'bin', concat('A-', lpad((id % 25)::text, 2, '0'))
    ),
    'flags',
    jsonb_build_array(
      CASE WHEN active THEN 'active' ELSE 'inactive' END,
      CASE WHEN featured THEN 'featured' ELSE 'standard' END
    )
  )
  """)

IO.puts("Metadata rows refreshed: #{seed_result.num_rows}")

Setup 4) Define JSON Helpers

This helper cell prints SQL/params and executes a query with a compact preview.

defmodule JsonLab.Helpers do
  @moduledoc false

  def explain(label, query) do
    {sql, params} = Selecto.to_sql(query)

    IO.puts("\n=== #{label} ===")
    IO.puts(String.trim(sql))
    IO.puts("Params: #{inspect(params)}")

    {sql, params}
  end

  def run(label, query, preview_count \\ 10) do
    explain(label, query)

    case Selecto.execute(query) do
      {:ok, {rows, columns, aliases}} = ok ->
        IO.puts("Rows: #{length(rows)}")
        IO.puts("Columns: #{inspect(columns)}")
        IO.puts("Aliases: #{inspect(aliases)}")
        IO.inspect(Enum.take(rows, preview_count), label: "Preview (up to #{preview_count} rows)")
        ok

      {:error, error} = failure ->
        IO.puts("Error: #{inspect(error)}")
        failure
    end
  end

  def run_shape(label, query, preview_count \\ 10) do
    explain(label, query)
    IO.inspect(query.set.selection_shape, label: "Compiled shape", pretty: true)

    case Selecto.execute_shape(query) do
      {:ok, shaped_rows} = ok ->
        IO.puts("Shaped rows: #{length(shaped_rows)}")
        IO.inspect(Enum.take(shaped_rows, preview_count), label: "Shape preview (up to #{preview_count} rows)")
        ok

      {:error, error} = failure ->
        IO.puts("Error: #{inspect(error)}")
        failure
    end
  end
end

1) Extract JSON Fields into Select Columns

Use json_select to project JSON paths into result columns alongside standard relational fields.

json_extract_query =
  Selecto.configure(config.product_domain, config.repo)
  |> Selecto.select(["name", "sku", "price"])
  |> Selecto.json_select([
    {:json_extract_text, "metadata", "$.price_band", as: "price_band"},
    {:json_extract_text, "metadata", "$.warehouse.zone", as: "warehouse_zone"},
    {:json_extract_text, "metadata", "$.flags[0]", as: "primary_flag"}
  ])
  |> Selecto.order_by({"price", :desc})
  |> Selecto.limit(10)

JsonLab.Helpers.run("JSON extraction", json_extract_query)

2) Filter by JSON Containment

Use json_filter with containment to keep only products whose JSON metadata declares price_band = premium.

json_filter_query =
  Selecto.configure(config.product_domain, config.repo)
  |> Selecto.select(["name", "sku", "price"])
  |> Selecto.json_select([
    {:json_extract_text, "metadata", "$.price_band", as: "price_band"},
    {:json_extract_text, "metadata", "$.warehouse.zone", as: "warehouse_zone"}
  ])
  |> Selecto.json_filter({:json_contains, "metadata", %{"price_band" => "premium"}})
  |> Selecto.filter({"active", true})
  |> Selecto.order_by({"price", :desc})
  |> Selecto.limit(10)

JsonLab.Helpers.run("JSON containment filter", json_filter_query)

3) Order by a JSON Path

Apply json_order_by to sort by JSON path output after relational ordering.

json_order_query =
  Selecto.configure(config.product_domain, config.repo)
  |> Selecto.select(["name", "sku", "price"])
  |> Selecto.json_select([
    {:json_extract_text, "metadata", "$.warehouse.zone", as: "warehouse_zone"},
    {:json_extract_text, "metadata", "$.warehouse.bin", as: "warehouse_bin"}
  ])
  |> Selecto.filter({"active", true})
  |> Selecto.order_by({"name", :asc})
  |> Selecto.json_order_by({:json_extract_text, "metadata", "$.warehouse.zone", :asc})
  |> Selecto.limit(10)

JsonLab.Helpers.run("JSON path ordering", json_order_query)

4) Combined Relational + JSON Query

Combine relational filters, JSON filters, JSON projections, and mixed ordering in one query.

combined_json_query =
  Selecto.configure(config.product_domain, config.repo)
  |> Selecto.select(["name", "sku", "price", "active"])
  |> Selecto.json_select([
    {:json_extract_text, "metadata", "$.price_band", as: "price_band"},
    {:json_extract_text, "metadata", "$.warehouse.zone", as: "warehouse_zone"},
    {:json_extract_text, "metadata", "$.flags[1]", as: "secondary_flag"}
  ])
  |> Selecto.filter({"price", {:>=, 25}})
  |> Selecto.json_filter({:json_contains, "metadata", %{"warehouse" => %{"zone" => "in_stock"}}})
  |> Selecto.order_by({"price", :desc})
  |> Selecto.json_order_by({:json_extract_text, "metadata", "$.price_band", :asc})
  |> Selecto.limit(15)

JsonLab.Helpers.run("Combined JSON + relational query", combined_json_query)

5) JSON Path Existence and Path-Match Filters

Use JSON existence checks for optional keys, then add path-based value matching.

optional_key_seed =
  config.repo.query!("""
  UPDATE products
  SET metadata =
    CASE
      WHEN featured THEN jsonb_set(
        coalesce(metadata, '{}'::jsonb),
        '{promo_code}',
        to_jsonb(concat('PROMO-', id::text)),
        true
      )
      ELSE coalesce(metadata, '{}'::jsonb) - 'promo_code'
    END
  """)

IO.puts("Optional promo_code keys refreshed: #{optional_key_seed.num_rows}")

json_existence_query =
  Selecto.configure(config.product_domain, config.repo)
  |> Selecto.select(["name", "sku", "price", "featured"])
  |> Selecto.json_select([
    {:json_extract_text, "metadata", "$.promo_code", as: "promo_code"},
    {:json_extract_text, "metadata", "$.warehouse.zone", as: "warehouse_zone"}
  ])
  |> Selecto.json_filter([
    {:json_path_exists, "metadata", "$.promo_code"},
    {:json_extract_text, "metadata", "$.warehouse.zone", {:=, "in_stock"}}
  ])
  |> Selecto.order_by({"price", :desc})
  |> Selecto.limit(10)

JsonLab.Helpers.run("JSON path existence + path match", json_existence_query)

6) JSON Filter vs Relational Filter (Same Rule)

price_band = premium was seeded from price >= 100. This section compares JSON and relational implementations of the same business rule.

premium_by_json_query =
  Selecto.configure(config.product_domain, config.repo)
  |> Selecto.select(["name", "sku", "price"])
  |> Selecto.json_select([{:json_extract_text, "metadata", "$.price_band", as: "price_band"}])
  |> Selecto.json_filter({:json_contains, "metadata", %{"price_band" => "premium"}})
  |> Selecto.order_by({"price", :desc})
  |> Selecto.limit(20)

premium_by_relational_query =
  Selecto.configure(config.product_domain, config.repo)
  |> Selecto.select(["name", "sku", "price"])
  |> Selecto.filter({"price", {:>=, 100}})
  |> Selecto.order_by({"price", :desc})
  |> Selecto.limit(20)

{:ok, {json_rows, _, _}} = JsonLab.Helpers.run("Premium by JSON price_band", premium_by_json_query, 5)
{:ok, {rel_rows, _, _}} = JsonLab.Helpers.run("Premium by relational price threshold", premium_by_relational_query, 5)

IO.puts("JSON rows: #{length(json_rows)}")
IO.puts("Relational rows: #{length(rel_rows)}")
IO.puts("Counts match? #{length(json_rows) == length(rel_rows)}")

7) Shaped Output with json_select/2 + select_shape/2

Project JSON-derived columns and package them into nested output tuples for structured rendering. When shaping, use explicit field selectors for JSON paths (rather than alias-only names) so field resolution remains valid.

json_shaped_query =
  Selecto.configure(config.product_domain, config.repo)
  |> Selecto.select(["name", "sku", "price"])
  |> Selecto.json_select([
    {:json_extract_text, "metadata", "$.price_band", as: "price_band"},
    {:json_extract_text, "metadata", "$.warehouse.zone", as: "warehouse_zone"}
  ])
  |> Selecto.select_shape([
    "name",
    {"sku", "price"},
    {
      {:field, "metadata.price_band", "price_band"},
      {:field, "metadata.warehouse.zone", "warehouse_zone"}
    }
  ])
  |> Selecto.order_by({"price", :desc})
  |> Selecto.limit(10)

JsonLab.Helpers.run_shape("Shaped JSON projection", json_shaped_query, 10)

Next Steps

To extend this workbook, add:

1. JSON aggregation examples (json_agg, json_object_agg) grouped by category or supplier
2. A mixed JSON + window-function section for ranked metadata cohorts
3. A compact Kino table section for side-by-side JSON and relational query result comparison