JsonB sorting within Resource.actions

I have a Resource with a jsonb column called metadata How do I default sort by `metadata ->> 'CreatedDate'?

actions do
    read :read do
      prepare build(sort: [expr(fragment("metadata->>'CreatedTime' DESC"))])   # <-- How do I do this?!
      filter expr(isproject == true)
    end
  end

actions do
    read :read do
      prepare build(sort: [expr(fragment("metadata->>'CreatedTime' DESC"))])   # <-- How do I do this?!
      filter expr(isproject == true)
    end
  end

5 Replies

ZachDaniel•2y ago

metadata[“CreatedTime”]

Terry LeeOP•2y ago

How do you do in descending order with that?

ZachDaniel•2y ago

oh, sorry, was tired when I read this You can't actually sort on expression right now what you'd do is add a calculation and sort on that

calculations do
  calculate :created_time, :utc_datetime, expr(metadata["CreatedTime"])
end

calculations do
  calculate :created_time, :utc_datetime, expr(metadata["CreatedTime"])
end

Then you'd say build(sort: [created_time: :desc])

Terry LeeOP•2y ago

thank you! works perfect! Somehow the generated Sql ended up double casting

[debug] QUERY OK source="qb_customers" db=968.5ms idle=1582.0ms
SELECT q0."id", .... FROM "qb_customers" AS q0 WHERE (q0."isproject"::boolean = $1::boolean) ORDER BY (q0."metadata"::jsonb #>> ARRAY['CreateTime'])::timestamp::timestamp DESC [true]

[debug] QUERY OK source="qb_customers" db=968.5ms idle=1582.0ms
SELECT q0."id", .... FROM "qb_customers" AS q0 WHERE (q0."isproject"::boolean = $1::boolean) ORDER BY (q0."metadata"::jsonb #>> ARRAY['CreateTime'])::timestamp::timestamp DESC [true]

See the double ::timestamp::timestamp at the back

ZachDaniel•2y ago

Yeah, that happens sometimes TBH its something we can fix but isn't a high priority since it doesn't really affect anything well, it affects query readability but not correctness

Gaming

Programming

JsonB sorting within Resource.actions

Did you find this page helpful?