1

I want to create a database index on json column on field customer_number which has the follwoing data..

{
    "version": "3.0",
    "order_header": {
        "platform": "CORE",
        "customer": {
            "contact_id": null,
            "customer_number": "12345",
            "customer_type": "PRIVATE"
}
}}

can you recommend a way to do this? I tried this but this throws me an error

CREATE INDEX idxcustomernumber ON sales_order USING GIN 
((original_order -> 'order_header' -> 'customer' -> 'customer_number'));

The error is

SQL Error [42704]: ERROR: data type JSON has no default operator class for access method "gin"
  Hint: You must specify an operator class for the index or define a default operator class for the data type.
2
  • 1
    Why do you think a GIN index is appropriate in this case?
    – mustaccio
    Feb 7 at 12:57
  • 1
    Or, to be more precise: what query do you want to speed up? Feb 7 at 16:28

0