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

in utopian-io •  4 months ago

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.

Links

Repo

Commits and changes

Implemented operators and functions

PG operatorright PG operand typeEctoPgJson functiondescription
->intget_object(jsonb, field)Get JSON array element
->textget_object(jsonb, field)Get JSON object field by key
->>intget_text(jsonb, field)Get JSON array element as text
->>textget_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
@>jsonbleft_cotains?(jsonb, jsonb)Does the left JSON value contain the right JSON path/value entries at the top level?
<@jsonbright_contains?(jsonb, jsonb)Are the left JSON path/value entries contained at the top level within the right JSON value?
?textkeys_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"}

(*`□)<炎炎炎炎

Authors get paid when people like you upvote their post.
If you enjoyed what you read here, create your account today and start earning FREE STEEM!
Sort Order:  

Thank you for your contribution. How it is different than Ecto.Query.API, because someone can directly do fragment("?->>? ILIKE ?", p.map, "key_name", ^some_value) for JSON/JSONB?

Your contribution has been evaluated according to Utopian policies and guidelines, as well as a predefined set of questions pertaining to the category.

To view those questions and the relevant answers related to your post, click here.


Need help? Write a ticket on https://support.utopian.io/.
Chat with us on Discord.
[utopian-moderator]

·

Thanks for the review, @codingdefined.

How it is different than Ecto.Query.API, because someone can directly do fragment("?->>? ILIKE ?", p.map, "key_name", ^some_value) for JSON/JSONB?

Yep, that's exactly the point of this library. I've noticed that my codebase is full of repeated fragments for JSONB handling so I've decided to dry it out and extract the functionality to a dedicated package.

Hey @ontofractal
Thanks for contributing on Utopian.
We’re already looking forward to your next contribution!

Want to chat? Join us on Discord https://discord.gg/h52nFrV.

Vote for Utopian Witness!

совсем веру в голос потерял?)

Hi , am from China, your article is very good.
i want to translate it and share your idea with people around me.
is that possible ?
Thank you so much for your sharing.