ECTO/SQL - How to use nested conditional logic (CASE WHEN) inside fragment?!

Questions / Help
1

This question might not strictly related to Elixir, but f someone could help me to learn I would really appreciate it!
So, I need to find a value in the DB and transform it based on a simple logic. Basically my fragment in select looks like this:

fragment("
        CASE
          WHEN ? IS NOT NULL THEN ?
          ELSE ? ->> 'key'
        END
      ", id, t1.key, t2.object)

And it works, but I need to apply a conditional logic for ? when I got the value following this logic or sudo code:

fragment("
        CASE
          WHEN id IS NOT NULL THEN 
             IF t1.key == "1", DO: "SUCCESS", ELSE: "FAIL"
          ELSE
             IF t2.object.key == "1", DO: "SUCCESS", ELSE: "FAIL"
        END
      ", id, t1.key, t2.object)

I was trying different approaches but always getting errors related to mismatch of received argument(s) vs expected argument(s).

Obviously, I can do that later after Repo.all, but I would like to put all load on DB level.

I would appreciate any help here!
Thanks a lot in advance!

2

Are you asking about accessing the key of a json field? If so you need to use the database’s access operator when using a fragment. Because fragment is just raw SQL with no Ecto magic.

For example this is Postgres’s documentation about the subject: PostgreSQL: Documentation: 15: 9.16. JSON Functions and Operators

And you have to be careful some operators utilize indexes and some don’t.

3

@joey_the_snake thanks a lot!
My question was more about how to properly structure a conditional logic, not how to access the key in json object.
Thanks a lot for the input though! I appreciate it!