Rails Select from JSON Array

Rails Select from JSON Array

Controller

@venues = JSON.parse @venues

View

<%= select(:model, :venue_id, @venues.map {|v| [ v['venue']['name'], v['venue']['id'] ] }) %>

Or to be more cleaner:

Controller

@venues = JSON.parse @venues
@venues_list = @venues.map { |v| v['venue'] }

View

<%= select(:model, :venue_id, @venues_list.map {|v| [ v['name'], v['id'] ] }) %>

More information about select helper.

Active Record query array of Json

The reason why you're getting a PG::UndefinedFunction: exception ("operator does not exist: json @> unknown") is because the @> operator is meant to be used in jsonb data type columns, and your products.category column isn't.

For that you can; or to update the data type of the category column, or to explicitly cast the column at the moment of performing the query:

Product.where('category::jsonb @> ?', [{ name: 'Sport' }].to_json)

This:

Product.where("category @> ?", "[{'name': 'Sport'}]")

isn't going to work, since it's not valid syntax.

Query on Postgres JSON array field in Rails

Assumptions:

• Postgres 9.4 or later.
• "get all the users that are in serie 5" is supposed to mean:
  
  "with at least one array element that contains {"serie": 5}. There may be others."
• Working with your first, shorter data format. No redundant 'data' key.

Short answer: Use jsonb instead of json and this just works:

User.where("groups @> ?", '[{"serie": 5}]')

Note the square brackets to make the right-hand operand a JSON array.

Why?

The prominent misunderstanding here: data type json is not the same as jsonb.

You didn't declare the actual table definition, but you later commented json and there is a hint in the question:

select json_array_elements(groups -> 'data') ->> 'serie' from users;

json_array_elements() only works for json, would have to be jsonb_array_elements() for jsonb. But you try to use the jsonb operator @> which is not defined for json:

groups -> 'data' @>  '?'

The operator -> returns the same type as the left-hand input. But @> is only defined for jsonb, not for json.

Then you try to use the operator @> for text as left-hand operand. Not possible either:

groups ->> 'data' @>  ?

There are variants of the operator @> for various types (incl. Postgres arrays), but not for text and not for json.

So, the short answer: Use jsonb instead of json. This allows to use very efficient indexes, too:

• Index for finding an element in a JSON array

json

For data type json you could use:

SELECT *
FROM   users u
WHERE  EXISTS (
   SELECT FROM json_array_elements(u.groups) elem 
   WHERE  elem ->> 'serie' = '5'
   );

Demos

jsonb:

SELECT *
FROM  (
   VALUES (1, jsonb '[{"serie":5, "year":3, "specialization":"Matematica", "management_id":1, "group_number":2}
                    , {"serie":5, "year":3, "specialization":"Matematica", "management_id":1, "group_number":2}]')
        , (2,       '[{"serie":7, "year":3, "specialization":"Matematica", "management_id":1, "group_number":2}
                    , {"serie":8, "year":3, "specialization":"Matematica", "management_id":1, "group_number":2}]')
        , (3,       '[{"serie":9, "year":3, "specialization":"Matematica", "management_id":1, "group_number":2}
                    , {"serie":5, "year":3, "specialization":"Matematica", "management_id":1, "group_number":2}]')
   ) users(id, groups)
WHERE  groups @> '[{"serie": 5}]';

json:

SELECT *
FROM  (
   VALUES (1, json  '[{"serie":5, "year":3, "specialization":"Matematica", "management_id":1, "group_number":2}
                    , {"serie":5, "year":3, "specialization":"Matematica", "management_id":1, "group_number":2}]')
        , (2,       '[{"serie":7, "year":3, "specialization":"Matematica", "management_id":1, "group_number":2}
                    , {"serie":8, "year":3, "specialization":"Matematica", "management_id":1, "group_number":2}]')
        , (3,       '[{"serie":9, "year":3, "specialization":"Matematica", "management_id":1, "group_number":2}
                    , {"serie":5, "year":3, "specialization":"Matematica", "management_id":1, "group_number":2}]')
   ) users(id, groups)
WHERE  EXISTS (
   SELECT FROM json_array_elements(users.groups) elem 
   WHERE  elem ->> 'serie'  = '5'
   );

How to query json data with hashes inside array in rails?

With raw SQL you can achieve with the below:

select * from notes,
  json_array_elements(items) element
  where element->>'description' LIKE '%PM%';

Try the below with activerecord:

Note.where("EXISTS (SELECT FROM json_array_elements(items) element WHERE element->>'description' LIKE '%PM%')")

Ruby on rails and JSON to create Select form

The easiest location to put your JSON file is in the same directory as your controllers -- although doing this kind of thing repeatedly will quickly make a mess out of your controllers folder.

Once you've done that you can read it from your controller like this:

file = File.read('./countries.json')
countries = JSON.parse(file)

and then map countries to the data type that options_for_select expects.

Once you have that working, I'd recommend making a helper that knows how to read the json file, cache it's data, and return it. Store the json file in the same directory as the helper. Reference the helper in your controller.

Also note: the JSON gem is required for this.

In your helper, you want to create this method:

def countries_for_select
    file = File.read('./countries.json')
    countries = JSON.parse(file)
    countries.map {|c| [ c['country']['label'], v['country']['country_code'] ] })
end

.map translates the fields from your countries json object to the [[name,key], ...] array that options_for_select expects.

Per the form helper on the Rails Documentation, the syntax for select_tag looks like this

<%= select_tag(:country, options_for_select(...)) %>

but we already created the helper countries_for_select to match the format that the select_tag is expecting. So all you need to do is this:

<%= select_tag(:country, countries_for_select) %>

LIKE Query on Postgres JSON array field in Rails

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%'")

Rails query interface: selecting rows in database where any of the JSON array's values match a certain criteria

Since the items is array at given example you can't work it out using only operators. You need to use jsonb_array_elements() in order to look into that.

Here's SQL query example to meet your requirement:

SELECT  *
FROM    publishing_rules
WHERE   EXISTS (
            SELECT  1
            FROM    jsonb_array_elements( menu_items -> 'items' )
            WHERE   value ->> 'id' = '2'
            LIMIT   1
        );

So, using within WHERE EXISTS lookup into the array does the trick.

Parse JSON to an Array in Rails

JSON.parse <string> is probably what you need here. It could look something like this:

test "works" do
  get :method

  result = JSON.parse(response.body)

  assert_equal 1, result.count
end

Check out "How to unit-test a JSON controller?" and the ActiveSupport JSON documention.

---

Related Topics