LIKE Query on Postgres JSON array field in Rails

Question

I have a table called messages which has a jsonB column called headers to store message headers. It looks like this:

[
   {
      "name":"Cc",
      "field":{
         "name":"Cc",
         "value":"\"[email protected]\" <[email protected]>",
         "length":null,
         "charset":"UTF-8",
         "element":null,
         "address_list":{
            "addresses":[
               {
                  "data":{
                     "raw":"\"[email protected]\" <[email protected]>",
                     "error":null,
                     "group":null,
                     "local":"abc",
                     "domain":"gmail.com",
                     "comments":[

                     ],
                     "display_name":"[email protected]",
                     "obs_domain_list":null
                  },
                  "parsed":true
               }
            ],
            "group_names":[

            ]
         }
      },
      "charset":"UTF-8",
      "field_order_id":14,
      "unparsed_value":"\"[email protected]\" <[email protected]>"
   },
   {
      "name":"Message-ID",
      "field":{
         "name":"Message-ID",
         "uniq":1,
         "value":"<[email protected]>",
         "length":null,
         "charset":"UTF-8",
         "element":{
            "message_ids":[
               "[email protected]"
            ]
         }
      },
      "charset":"UTF-8",
      "field_order_id":16,
      "unparsed_value":"<[email protected]>"
   },
   {
      "name":"Subject",
      "field":{
         "name":"Subject",
         "value":"Re: test email",
         "errors":[

         ],
         "length":null,
         "charset":"UTF-8",
         "element":null
      },
      "charset":"UTF-8",
      "field_order_id":19,
      "unparsed_value":"Re: test email"
   }
]

I want search records where 'name' = 'Subject' and 'unparsed_value' like %test% and return the result in Rails 6.0.2?

I'm trying the below mention code:

messages.where("headers @> '[{\"name\": \"Subject\"}, {\"unparsed_value\" LIKE \"%test%\"}]'")

But it's throwing error!

Sebastián Palma · Accepted Answer · 2020-07-08 08:15:03Z

3

You can do a sub query to get the elements you need to compare and then use them in the where clause:

Message
  .from(
    Message.select("
      id,
      headers,
      jsonb_array_elements(headers)->>'unparsed_value' AS unparsed_value,
      jsonb_array_elements(headers)->>'name' AS name
    "), :t
  )
  .select('t.*')
  .where("t.name = 'Subject' AND t.unparsed_value LIKE '%test%'")

edited Jul 8, 2020 at 8:15

answered Jul 6, 2020 at 19:33

Sebastián Palma

33.6k6 gold badges45 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Rivu Over a year ago

It's returning empty array!

Les Nightingill Over a year ago

@SebastianPalma I believe the headers column in the question contains an array of hashes. Yours contains a single hash.

Sebastián Palma Over a year ago

Right @LesNightingill, just updated that. Thanks for pointing that out.

Rivu Over a year ago

thanks @LesNightingill. It worked! May I know why it is needed to do self inner query on Message table?

Sebastián Palma Over a year ago

Not a self inner @Rivu (join?), it's just you need somehow to access those two columns/values to use them in your where clause. The most common is to think you can get them in your select statement, but values there aren't able to be used within the where, so from is the way to go.

Les Nightingill · Accepted Answer · 2020-07-08 05:37:55Z

1

The query you need looks like this:

SELECT * FROM messages WHERE 
  (SELECT true FROM jsonb_to_recordset(messages.headers)
    AS x(name text, field jsonb)
    WHERE name = 'Subject'
    AND field->>'unparsed_value' LIKE '%test%');

Does this give you the result you're looking for?

answered Jul 8, 2020 at 5:37

Les Nightingill

6,1742 gold badges32 silver badges34 bronze badges

Collectives™ on Stack Overflow

LIKE Query on Postgres JSON array field in Rails

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related