Paste this into a new question or an answer at dba.stackexchange.com:
<!-- --> > CREATE TABLE test( > id integer, > json_key json[] > ); > > INSERT INTO test (id, json_key) VALUES > (1,array['{"key1":"123"}'::json,'{"key2":"456"}'::json]), > (2,array['{"key1":"789"}'::json,'{"key2":"456"}'::json]); > > <pre> > ✓ > 2 rows affected > </pre> <!-- --> > SELECT * FROM test WHERE '{"key2": "456"}'::jsonb <@ ANY(json_key::jsonb[]); > > <pre> > id | json_key > -: | :------------------------------------------ > 1 | {&quot;{\&quot;key1\&quot;:\&quot;123\&quot;}&quot;,&quot;{\&quot;key2\&quot;:\&quot;456\&quot;}&quot;} > 2 | {&quot;{\&quot;key1\&quot;:\&quot;789\&quot;}&quot;,&quot;{\&quot;key2\&quot;:\&quot;456\&quot;}&quot;} > </pre> <!-- --> > SELECT * FROM test WHERE '{"key1": "456"}'::jsonb <@ ANY(json_key::jsonb[]); > > <pre> > id | json_key > -: | :------- > </pre> <!-- --> > SELECT * FROM test WHERE '{"key1": "123"}'::jsonb <@ ANY(json_key::jsonb[]); > > <pre> > id | json_key > -: | :------------------------------------------ > 1 | {&quot;{\&quot;key1\&quot;:\&quot;123\&quot;}&quot;,&quot;{\&quot;key2\&quot;:\&quot;456\&quot;}&quot;} > </pre> <!-- --> > -------------------------------------------------------------------------------- > -- ② Graphs > -------------------------------------------------------------------------------- > -- "§Chart§" is a magic value to recognize the output as graph data > -- this is a live dbfiddle stats graph but any data can be used > -- dbfiddle stats are only accessible inside the Postgres 9.6 backend > -------------------------------------------------------------------------------- > select to_char(date_trunc('day',history_hour_at),'YYYY-MM-DD') "§Chart§" > , coalesce(sum(distinct_ip) filter (where rdbms_code~'^sqlserver'),0) "SQL Server" > , coalesce(sum(distinct_ip) filter (where rdbms_code~'^postgres'),0) "Postgres" > , coalesce(sum(distinct_ip) filter (where rdbms_code~'^oracle'),0) "Oracle" > , coalesce(sum(distinct_ip) filter (where rdbms_code~'^maria'),0) "MariaDB" > , coalesce(sum(distinct_ip) filter (where rdbms_code~'^sqlite'),0) "SQLite" > from stats.rdbms > group by date_trunc('day',history_hour_at) > order by 1; > > <pre> > §Chart§ | SQL Server | Postgres | Oracle | MariaDB | SQLite > :--------- | ---------: | -------: | -----: | ------: | -----: > 2017-02-27 | 5 | 0 | 0 | 0 | 0 > 2017-02-28 | 28 | 4 | 7 | 0 | 0 > 2017-03-01 | 12 | 8 | 17 | 0 | 0 > 2017-03-02 | 50 | 6 | 10 | 0 | 0 > 2017-03-03 | 33 | 0 | 6 | 0 | 0 > 2017-03-04 | 8 | 1 | 3 | 0 | 0 > 2017-03-05 | 11 | 9 | 4 | 0 | 0 > 2017-03-06 | 15 | 14 | 4 | 0 | 0 > 2017-03-07 | 24 | 53 | 11 | 0 | 0 > 2017-03-08 | 30 | 23 | 13 | 0 | 0 > 2017-03-09 | 36 | 33 | 4 | 0 | 0 > 2017-03-10 | 33 | 24 | 12 | 0 | 0 > 2017-03-11 | 41 | 20 | 1 | 0 | 0 > 2017-03-12 | 22 | 17 | 7 | 0 | 0 > 2017-03-13 | 15 | 23 | 6 | 0 | 0 > 2017-03-14 | 24 | 14 | 9 | 0 | 0 > 2017-03-15 | 16 | 10 | 5 | 1 | 0 > 2017-03-16 | 37 | 15 | 10 | 0 | 0 > 2017-03-17 | 12 | 16 | 4 | 1 | 0 > 2017-03-18 | 19 | 13 | 8 | 0 | 0 > 2017-03-19 | 55 | 12 | 6 | 3 | 0 > 2017-03-20 | 25 | 10 | 20 | 3 | 0 > 2017-03-21 | 33 | 7 | 7 | 0 | 0 > 2017-03-22 | 26 | 19 | 19 | 1 | 0 > 2017-03-23 | 24 | 14 | 13 | 1 | 1 > 2017-03-24 | 47 | 23 | 7 | 2 | 0 > 2017-03-25 | 59 | 7 | 6 | 0 | 0 > 2017-03-26 | 32 | 7 | 5 | 2 | 0 > 2017-03-27 | 50 | 10 | 13 | 1 | 0 > 2017-03-28 | 35 | 13 | 10 | 2 | 1 > 2017-03-29 | 67 | 13 | 7 | 2 | 2 > 2017-03-30 | 65 | 27 | 10 | 0 | 0 > 2017-03-31 | 45 | 19 | 6 | 1 | 1 > 2017-04-01 | 65 | 9 | 3 | 4 | 0 > 2017-04-02 | 33 | 9 | 3 | 1 | 0 > 2017-04-03 | 71 | 51 | 46 | 0 | 2 > 2017-04-04 | 44 | 21 | 26 | 1 | 1 > 2017-04-05 | 42 | 9 | 13 | 4 | 0 > 2017-04-06 | 34 | 17 | 8 | 2 | 0 > 2017-04-07 | 23 | 10 | 16 | 5 | 0 > 2017-04-08 | 11 | 6 | 4 | 2 | 0 > 2017-04-09 | 20 | 12 | 4 | 3 | 0 > 2017-04-10 | 34 | 11 | 6 | 4 | 0 > 2017-04-11 | 47 | 23 | 23 | 0 | 0 > 2017-04-12 | 43 | 29 | 14 | 5 | 0 > 2017-04-13 | 22 | 10 | 7 | 8 | 0 > 2017-04-14 | 20 | 7 | 13 | 3 | 0 > 2017-04-15 | 44 | 11 | 5 | 5 | 0 > 2017-04-16 | 35 | 8 | 5 | 0 | 0 > 2017-04-17 | 20 | 17 | 9 | 1 | 0 > 2017-04-18 | 38 | 16 | 6 | 2 | 0 > 2017-04-19 | 39 | 14 | 9 | 0 | 1 > 2017-04-20 | 67 | 20 | 12 | 0 | 3 > 2017-04-21 | 37 | 16 | 10 | 0 | 1 > 2017-04-22 | 18 | 25 | 4 | 2 | 0 > 2017-04-23 | 6 | 7 | 1 | 2 | 0 > 2017-04-24 | 22 | 21 | 4 | 3 | 0 > 2017-04-25 | 28 | 25 | 2 | 7 | 0 > 2017-04-26 | 44 | 19 | 13 | 23 | 1 > 2017-04-27 | 7 | 1 | 1 | 4 | 0 > 2017-05-01 | 24 | 9 | 5 | 1 | 0 > 2017-05-02 | 25 | 20 | 6 | 5 | 0 > 2017-05-03 | 25 | 23 | 11 | 2 | 0 > 2017-05-04 | 30 | 17 | 9 | 1 | 0 > 2017-05-05 | 11 | 21 | 5 | 2 | 0 > 2017-05-06 | 18 | 12 | 7 | 1 | 0 > 2017-05-07 | 8 | 11 | 4 | 1 | 0 > 2017-05-08 | 27 | 24 | 19 | 4 | 0 > 2017-05-09 | 23 | 19 | 15 | 3 | 0 > 2017-05-10 | 31 | 29 | 12 | 0 | 0 > 2017-05-11 | 29 | 23 | 18 | 15 | 1 > 2017-05-12 | 28 | 17 | 7 | 3 | 0 > 2017-05-13 | 24 | 15 | 11 | 5 | 0 > 2017-05-14 | 5 | 5 | 3 | 0 | 0 > 2017-05-15 | 28 | 54 | 10 | 1 | 0 > 2017-05-16 | 22 | 24 | 11 | 2 | 0 > 2017-05-17 | 54 | 12 | 14 | 1 | 1 > 2017-05-18 | 54 | 39 | 17 | 7 | 1 > 2017-05-19 | 13 | 43 | 10 | 4 | 0 > 2017-05-20 | 27 | 17 | 1 | 6 | 0 > 2017-05-21 | 18 | 16 | 2 | 6 | 0 > 2017-05-22 | 30 | 15 | 9 | 3 | 0 > 2017-05-23 | 54 | 27 | 8 | 8 | 0 > 2017-05-24 | 43 | 27 | 6 | 5 | 0 > 2017-05-25 | 40 | 24 | 7 | 3 | 0 > 2017-05-26 | 41 | 22 | 11 | 0 | 0 > 2017-05-27 | 39 | 36 | 9 | 7 | 1 > 2017-05-28 | 30 | 32 | 8 | 9 | 3 > 2017-05-29 | 26 | 32 | 11 | 4 | 0 > 2017-05-30 | 27 | 27 | 15 | 7 | 0 > 2017-05-31 | 29 | 30 | 14 | 7 | 0 > 2017-06-01 | 33 | 16 | 11 | 3 | 0 > 2017-06-02 | 33 | 20 | 11 | 5 | 1 > 2017-06-03 | 26 | 10 | 3 | 3 | 0 > 2017-06-04 | 24 | 15 | 3 | 3 | 0 > 2017-06-05 | 31 | 21 | 5 | 2 | 0 > 2017-06-06 | 36 | 12 | 5 | 2 | 0 > 2017-06-07 | 35 | 31 | 6 | 3 | 0 > 2017-06-08 | 25 | 19 | 4 | 13 | 1 > 2017-06-09 | 26 | 16 | 9 | 9 | 1 > 2017-06-10 | 15 | 17 | 3 | 5 | 0 > 2017-06-11 | 22 | 13 | 2 | 3 | 0 > 2017-06-12 | 18 | 24 | 7 | 0 | 0 > 2017-06-13 | 19 | 15 | 19 | 8 | 0 > 2017-06-14 | 23 | 17 | 7 | 6 | 3 > 2017-06-15 | 31 | 15 | 5 | 4 | 1 > 2017-06-16 | 32 | 22 | 7 | 5 | 0 > 2017-06-17 | 19 | 12 | 4 | 11 | 0 > 2017-06-18 | 11 | 4 | 4 | 0 | 0 > 2017-06-19 | 28 | 12 | 10 | 26 | 3 > 2017-06-20 | 42 | 19 | 10 | 4 | 1 > 2017-06-21 | 41 | 9 | 15 | 5 | 0 > 2017-06-22 | 53 | 19 | 12 | 6 | 0 > 2017-06-23 | 30 | 29 | 7 | 13 | 0 > 2017-06-24 | 13 | 33 | 1 | 22 | 1 > 2017-06-25 | 12 | 24 | 5 | 10 | 0 > 2017-06-26 | 15 | 30 | 7 | 9 | 1 > 2017-06-27 | 16 | 29 | 10 | 7 | 0 > 2017-06-28 | 32 | 19 | 7 | 7 | 0 > 2017-06-29 | 24 | 28 | 14 | 11 | 0 > 2017-06-30 | 33 | 32 | 6 | 11 | 0 > 2017-07-01 | 24 | 15 | 1 | 14 | 1 > 2017-07-02 | 39 | 20 | 8 | 15 | 0 > 2017-07-03 | 28 | 33 | 15 | 10 | 0 > 2017-07-04 | 35 | 40 | 19 | 11 | 1 > 2017-07-05 | 28 | 29 | 25 | 5 | 2 > 2017-07-06 | 39 | 20 | 10 | 5 | 2 > 2017-07-07 | 28 | 24 | 7 | 9 | 0 > 2017-07-08 | 25 | 21 | 6 | 16 | 1 > 2017-07-09 | 28 | 23 | 7 | 14 | 3 > 2017-07-10 | 18 | 28 | 10 | 13 | 0 > 2017-07-11 | 25 | 28 | 12 | 1 | 0 > 2017-07-12 | 39 | 26 | 10 | 13 | 3 > 2017-07-13 | 38 | 29 | 5 | 9 | 0 > 2017-07-14 | 25 | 32 | 6 | 33 | 2 > 2017-07-15 | 25 | 38 | 16 | 14 | 0 > 2017-07-16 | 26 | 32 | 7 | 10 | 0 > 2017-07-17 | 13 | 12 | 0 | 10 | 0 > </pre> *db<>fiddle [here](https://dbfiddle.uk/?rdbms=postgres_9.6&fiddle=7f76a7901e93b9404e6171417d5bf25d)*
back to fiddle