EctoPgJson 0.1 release: Elixir/Ecto extension for Posgres JSON/JSONB operators

ontofractal(68)

Published in

#utopian-io

Words

172

Reading

1 min

Listen

Play

I've released EctoPgJson, a new Elixir/Erlang library for Postgres JSON operators for JSON and JSONB datatypes. EctoPgJson provides a macro-based utility functions for Ecto queries.

Implemented operators and functions

PG operator	right PG operand type	EctoPgJson function	description
->	int	get_object(jsonb, field)	Get JSON array element
->	text	get_object(jsonb, field)	Get JSON object field by key
->>	int	get_text(jsonb, field)	Get JSON array element as text
->>	text	get_text(jsonb, field)	Get JSON object field as text
#>	text[]	get_json_in(jsonb, field)	Get JSON object at specified path
#>>	text[]	get_text_in(jsonb, field)	Get JSON object at specified path as text
@>	jsonb	left_cotains?(jsonb, jsonb)	Does the left JSON value contain the right JSON path/value entries at the top level?
<@	jsonb	right_contains?(jsonb, jsonb)	Are the left JSON path/value entries contained at the top level within the right JSON value?
?	text	keys_exist?(jsonb, key)	Does the string exist as a top-level key within the JSON value?
?\|	text[]	keys_exist?(jsonb, any: strings)	Do any of these array strings exist as top-level keys?
?&	text[]	keys_exist?(jsonb, all: strings)	Do all of these array strings exist as top-level keys?

Example data

      create table metrics (
        id serial primary key,
        attributes jsonb
      );
  
      insert into metrics (id, attributes)
      values
        (1, '{"data": "nope"}'),
        (2, '{"data": {"level2": "yep"}}'),
        (3, '{"data": {"level2": {"level3": "yepyep"}}}');

Example queries

   q =
     from t in @table,
       where: get_text(t.attributes, "data") == "nope",
       select: t.id

   assert TestRepo.one(q) == 1

   q =
     from t in @table,
       where: t.id == 2,
       select: get_object(t.attributes, "data")

   assert TestRepo.one(q) == %{"level2" => "yep"}

  q =
    from t in @table,
      where: t.id == 2,
      select: get_text_in(t.attributes, "{data, level2}")

  q2 =
    from t in @table,
      where: t.id == 2,
      select: get_text_in(t.attributes, ["data", "level2"])

  assert TestRepo.one(q) == TestRepo.one(q2)
  assert TestRepo.one(q) == "yep"

  q =
    from t in @table,
      where: t.id == 3,
      select: get_object_in(t.attributes, "{data, level2}")

  q2 =
    from t in @table,
      where: t.id == 3,
      select: get_object_in(t.attributes, ["data", "level2"])

  assert TestRepo.one(q) == TestRepo.one(q2)
  assert TestRepo.one(q) == %{"level3" => "yepyep"}