clear markdown compare help best fiddles feedback dbanow.uk
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2591633 fiddles created (45700 in the last week).

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]);
2 rows affected
 hidden batch(es)


SELECT * FROM test WHERE '{"key2": "456"}'::jsonb <@ ANY(json_key::jsonb[]);
id json_key
1 {"{\"key1\":\"123\"}","{\"key2\":\"456\"}"}
2 {"{\"key1\":\"789\"}","{\"key2\":\"456\"}"}
 hidden batch(es)


SELECT * FROM test WHERE '{"key1": "456"}'::jsonb <@ ANY(json_key::jsonb[]);
id json_key
 hidden batch(es)


SELECT * FROM test WHERE '{"key1": "123"}'::jsonb <@ ANY(json_key::jsonb[]);
id json_key
1 {"{\"key1\":\"123\"}","{\"key2\":\"456\"}"}
 hidden batch(es)


-------------------------------------------------------------------------------- -- ② 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;
§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
 hidden batch(es)