JSON field in where clause

[neel]

My table has a json column named properties. Currently I am using that field as text. But now I need to query the json attributes in where clause.

I'd like a feature like where(reports.properties["colour"] == "red") which will be translated to properties ->> 'colour' = 'red'

Can there be any hackish way to achieve this ? Like a special field type ? I was trying like the following. But I believe there would be some easy way to incorporate the existing operator overloading templates instead of writing one from scratch.

  template <typename Lhs>
  struct associative_t
  {
    using _lhs_t = Lhs;

    associative_t(_lhs_t lhs, const std::string& rhs) : _lhs(lhs), _rhs(rhs) {}
    associative_t(const associative_t&) = default;
    associative_t(associative_t&&) = default;
    associative_t& operator=(const associative_t&) = default;
    associative_t& operator=(associative_t&&) = default;
    ~associative_t() = default;

    _lhs_t _lhs;
    std::string _rhs;
    
    // TODO need operator overload
  };
  
  template <typename Context, typename Lhs>
  struct serializer_t<Context, associative_t<Lhs>>
  {
    using T = associative_t<Lhs>;
    using _serialize_check = serialize_check_of<Context, typename T::_lhs_t>;

    static Context& _(const T& t, Context& context)
    {
      serialize(simple_column(t._lhs), context);
      context << "->>";
      serialize_operand(t._rhs, context);
      return context;
    }
  };

[matthijs]

Oh, that's a nice feature to have.

I think the best way to implement this is to create a new data_type for JSON, see for an example the uuid data type in this library. In that data type it should be possible to define operators to handle this situation. Thats the wild guess I have.

@rbock could you confirm this is the way to do this?

[rbock]

In general, yes, I think so.

At one point, I also thought about using a library like nlohman::json. That might make some usage nicer, but it would also dictate the JSON library.

Thus, a data type that uses text representation internally and offers a couple of special operators/functions is probably the way to go.