Your Location is: Home > Postgresql-9.5

Can't find record filter by zero array item

From: Islamabad View: 2385 a_subscriber 

Question

my_column has type jsonb

here json

{
  "id": 4107,
  "states": [
    {
      "dt": "2020-11-06T10:24:30.277+0000",
      "id": "order.new"
    }
  ]
}

I need to find all records where states[0].id="order.new" (zero item in array)

I try this

SELECT * FROM history WHERE my_column @> '{states,0, id}'= 'order.new'
limit 10

But I get error:

ERROR:  invalid input syntax for type json
LINE 1: SELECT * FROM history WHERE my_column @> '{states,0, id}'= 'or...

Best answer

The @> operator tests whether the right operand is contained in the left operand. The right operand must be valid Json document or a literal, that's why you're getting a syntax error.

The operator you want is #>, which uses the array as the path for extraction:

# SELECT * FROM history WHERE my_column #> '{states,0, id}' = '"order.new"'
limit 10;
 id |                                      my_column                                      
----+-------------------------------------------------------------------------------------
  1 | {"id": 4107, "states": [{"dt": "2020-11-06T10:24:30.277+0000", "id": "order.new"}]}
(1 row)

With the @> operator you could do the following, but it would be checking for matching elements in any array position, not only index 0:

# SELECT * FROM history WHERE my_column @> '{"states": [{ "id": "order.new" }]}';
 id |                                                                    my_column                                                                    
----+-------------------------------------------------------------------------------------------------------------------------------------------------
  1 | {"id": 4107, "states": [{"dt": "2020-11-06T10:24:30.277+0000", "id": "order.new"}]}
  4 | {"id": 4107, "states": [{"dt": "2020-11-06T10:24:30.333+0000", "id": "order.test"}, {"dt": "2020-11-06T10:24:33.333+0000", "id": "order.new"}]}