postgresql-9.5 querying jsonb column containing array of hashes

I am using postgresql-9.5. I want to query the jsonb column shown below that holds an array of hashes to return all the json where kind = email and also count the number of json rows that meets that criteria. The sql I am using is shown underneath the json data. Running the query throws the error: PG::InvalidTextRepresentation: ERROR: invalid input syntax for type json LINE 2: FROM jsonb_array_elements.

I have tried the queries suggested here and here.

This is how my jsonb data looks like:

  a = [   {"kind"=>"person", "filter_term"=>"56", "selected_attr"=>"customer", "limit"=>"1", "selected_operator"=>"less_than"},    {"kind"=>"email", "filter_term"=>"marketer", "selected_attr"=>"job_title", "selected_operator"=>"equals"}  ] 

Here is the sql query that throws the error:

s = "SELECT *       FROM   jsonb_array_elements('#{a}' ) AS elem       WHERE elem ->> 'kind' = 'email';" 

The table:

create_table "segments", force: :cascade do |t|   t.jsonb    "payload",    default: "[]", null: false   t.index ["payload"], name: "index_segments_on_payload", using: :gin end 

Replay

Category: sql Time: 2016-07-31 Views: 3

Related post

iOS development

Android development

Python development

JAVA development

Development language

PHP development

Ruby development

search

Front-end development

Database

development tools

Open Platform

Javascript development

.NET development

cloud computing

server

Copyright (C) avrocks.com, All Rights Reserved.

processed in 0.163 (s). 12 q(s)