jsonb in Postgres 9.4+

You can use the same query as for 9.3+ below, just with jsonb_array_elements().

But you should rather use the jsonb "contains" operator @> in combination with a matching GIN index on the expression data->'objects':

CREATE INDEX reports_data_gin_idx ON reports
USING gin ((data->'objects') jsonb_path_ops);

SELECT * FROM reports WHERE data->'objects' @> '[{"src":"foo.png"}]';

Since the key objects holds a JSON array, we need to match the structure in the search term and wrap the array element into square brackets, too. Drop the array brackets when searching a plain record.

More explanation and options:

• Index for finding an element in a JSON array

json in Postgres 9.3+

Unnest the JSON array with the function json_array_elements() in a lateral join in the FROM clause and test for its elements:

SELECT data::text, obj
FROM   reports r, json_array_elements(r.data#>'{objects}') obj
WHERE  obj->>'src' = 'foo.png';

db<>fiddle here
Old sqlfiddle

Or, equivalent for just a single level of nesting:

SELECT *
FROM   reports r, json_array_elements(r.data->'objects') obj
WHERE  obj->>'src' = 'foo.png';

->>, -> and #> operators are explained in the manual.

Both queries use an implicit JOIN LATERAL.

Closely related:

• Query for element of array in JSON column

you have to unnest the array of json-objects first using the function (json_array_elements or jsonb_array_elements if you have jsonb data type), then you can access the values by specifying the key.

WITH json_test (col) AS (
  values (json '[{"name":"Mickey Mouse","age":10},{"name":"Donald Duck","age":5}]')
)
SELECT
  y.x->'name' "name"
FROM json_test jt, 
LATERAL (SELECT json_array_elements(jt.col) x) y

-- outputs:
name
--------------
"Mickey Mouse"
"Donald Duck"

To get a count of unique names, its a similar query to the above, except the count distinct aggregate function is applied to y.x->>name

WITH json_test (col) AS (
  values (json '[{"name":"Mickey Mouse","age":10},{"name":"Donald Duck","age":5}]')
)
SELECT
  COUNT( DISTINCT y.x->>'name') distinct_names
FROM json_test jt, 
LATERAL (SELECT json_array_elements(jt.col) x) y

It is necessary to use ->> instead of -> as the former (->>) casts the extracted value as text, which supports equality comparison (needed for distinct count), whereas the latter (->) extracts the value as json, which does not support equality comparison.

Alternatively, convert the json as jsonb and use jsonb_array_elements. JSONB supports the equality comparison, thus it is possible to use COUNT DISTINCT along with extraction via ->, i.e.

COUNT(DISTINCT (y.x::jsonb)->'name')

updated answer for postgresql versions 12+

It is now possible to extract / unnest specific keys from a list of objects using jsonb path queries, so long as the field queried is jsonb and not json.

example:

WITH json_test (col) AS (
  values (jsonb '[{"name":"Mickey Mouse","age":10},{"name":"Donald Duck","age":5}]')
)
SELECT jsonb_path_query(col, '$[*].name') "name"
FROM json_test
-- replaces this original snippet:
-- SELECT
--   y.x->'name' "name"
-- FROM json_test jt, 
-- LATERAL (SELECT json_array_elements(jt.col) x) y

There is no single operation, which can help you, but you have a few options:

1. If you have a small (and fixed) number of ids to query, you can use multiple containment operators @> combined with or; f.ex.:

where data @> '[{"id": "1884595530"}]' or data @> '[{"id": "791712670"}]'

A simple gin index can help you on your data column here.

2. If you have variable number of ids (or you have a lot of them), you can use json[b]_array_elements() to extract each element of the array, build up an id list and then query it with the any-containment operator ?|:

select *
from   jsonbtest
where  to_json(array(select jsonb_array_elements(data) ->> 'id'))::jsonb ?|
         array['1884595530', '791712670'];

Unfortunately, you cannot index an expression, which has a sub-query in it. If you want to index it, you need to create a function for it:

create function idlist_jsonb(jsonbtest)
  returns jsonb
  language sql
  strict
  immutable
as $func$
  select to_json(array(select jsonb_array_elements($1.data) ->> 'id'))::jsonb
$func$;

create index on jsonbtest using gin (idlist_jsonb(jsonbtest));

After this, you can query ids like this:

select *, jsonbtest.idlist_jsonb
from   jsonbtest
where  jsonbtest.idlist_jsonb ?| array['193623800', '895207852'];

Note: I used dot notation / computed field here, but you don't have to.

3. But at this point, you don't have to stick with json[b]: you have a simple text array, which is supported by PostgreSQL too.

create function idlist_array(jsonbtest)
  returns text[]
  language sql
  strict
  immutable
as $func$
  select array(select jsonb_array_elements($1.data) ->> 'id')
$func$;

create index on jsonbtest using gin (idlist_array(jsonbtest));

And query this computed field with the overlap array operator &&:

select *, jsonbtest.idlist_array
from   jsonbtest
where  jsonbtest.idlist_array && array['193623800', '895207852'];

Note: From my internal testing, this latter solution is calculated with a higher cost than the jsonb variant, but in fact it is faster than that, a little. If performance really matters to you, you should test both.

It would be much more efficient to store your values in a normalized schema. That said, you can also make it work with your current setup.

Assumptions

Assuming this table definition:

CREATE TABLE tbl (tbl_id int, usr jsonb);

"user" is a reserved word and would require double quoting to be used as column name. Don't do that. I use usr instead.

Query

The query is not as trivial as the (now deleted) comments made it seem:

SELECT t.tbl_id, obj.val->>'count' AS count
FROM   tbl t
JOIN   LATERAL jsonb_array_elements(t.usr) obj(val) ON obj.val->>'_id' = '1'
WHERE  t.usr @> '[{"_id":"1"}]';

There are 3 basic steps:

1. Identify qualifying rows cheaply

WHERE t.usr @> '[{"_id":"1"}]' identifies rows with matching object in the JSON array. The expression can use a generic GIN index on the jsonb column, or one with the more specialized operator class jsonb_path_ops:

CREATE INDEX tbl_usr_gin_idx ON tbl USING gin (usr jsonb_path_ops);

The added WHERE clause is logically redundant, but it is required to use the index. The expression in the join clause enforces the same condition but only after unnesting the array in every row qualifying so far. With index support, Postgres only processes rows that contain a qualifying object to begin with. Does not matter much with small tables, makes a huge difference with big tables and only few qualifying rows.

Related:

• PostgreSQL operator uses index but underlying function does not
• Index for finding an element in a JSON array

2. Identify matching object(s) in the array

Unnest with jsonb_array_elements(). (unnest() is only good for Postgres array types.) Since we are only interested in actually matching objects, filter in the join condition right away.

Related:

• How to turn JSON array into Postgres array?

3. Extract value for nested key 'count'

After qualifying objects have been extracted, simply: obj.val->>'count'.