SQLize Online / PHPize Online  /  SQLtest Online

A A A
Share      Blog   Popular
Copy Format Clear
CREATE TABLE `bids` ( `id` varchar(36) NOT NULL COMMENT 'bid id from vendor', `buyer_id` varchar(36) NOT NULL COMMENT 'buyer_id from vendor', `buyer_account_id` varchar(36) NOT NULL COMMENT 'buyer_account_id from vendor', `country_id` varchar(4) DEFAULT NULL COMMENT 'country id code - like lv', `end_date` datetime DEFAULT NULL, `closed_at` datetime DEFAULT NULL, `exclusions` json DEFAULT NULL COMMENT 'json', `language_ids` json NOT NULL COMMENT 'json', `name` varchar(36) DEFAULT NULL, `published_at` datetime DEFAULT NULL, `quotas` json DEFAULT NULL COMMENT 'json', `state` varchar(10) NOT NULL COMMENT 'active paused ...', `statistics` json DEFAULT NULL COMMENT 'json', `supplier_exclusive` int DEFAULT NULL, `survey_type` varchar(36) DEFAULT NULL COMMENT 'like adhock', `timeout` int unsigned DEFAULT NULL COMMENT 'IP Address', `topic_id` varchar(36) DEFAULT NULL COMMENT 'like general', `created` datetime DEFAULT NULL, `modified` datetime DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='MorningConsult bids income log' ; -- //////////////////// INSERT INTO bids(`id`, `buyer_id`, `buyer_account_id`, `country_id`, `end_date`, `closed_at`, `exclusions`, `language_ids`, `name`, `published_at`, `quotas`, `state`, `statistics`, `supplier_exclusive`, `survey_type`, `timeout`, `topic_id`, `created`, `modified`) VALUES ('43a709b6-7ae8-4dae-89c6-9f48b4cda829', 'cac4cb9c-c416-4679-969e-ec6962c61303', 'ab180f06-aa2b-4b8b-9b87-1031bfe8b16b', 'es', '2023-05-04 08:01:32', NULL, '[{\"group_id\": \"75edde10-973b-52ad-9c83-f7281f52e595\", \"lockout_period\": 8}]', '[\"es\"]', 'Daily Tracker Survey', '2023-05-03 08:01:33', '[{\"id\": \"ce06db34-4d73-48a0-aeb9-ea8c22c64f45\", \"statistics\": {\"num_failures\": 0, \"num_timeouts\": 0, \"num_available\": 5, \"num_completes\": 3, \"num_qualified\": 8, \"num_in_progress\": 4, \"num_over_quotas\": 0, \"length_of_interview\": 1080, \"qualified_conversion\": 100, \"num_quality_terminations\": 1}, \"qualifications\": [{\"id\": \"age\", \"response_ids\": [\"18\", \"19\", \"20\", \"21\", \"22\", \"23\", \"24\", \"25\", \"26\", \"27\", \"28\", \"29\", \"30\", \"31\", \"32\", \"33\", \"34\"]}, {\"id\": \"education\", \"response_ids\": [\"8\", \"9\"]}, {\"id\": \"gender\", \"response_ids\": [\"2\"]}], \"cost_per_interview\": 15}, {\"id\": \"4c91208c-6a8e-41c9-923c-f67beae2d9a2\", \"statistics\": {\"num_failures\": 0, \"num_timeouts\": 0, \"num_available\": 0, \"num_completes\": 7, \"num_qualified\": 25, \"num_in_progress\": 8, \"num_over_quotas\": 0, \"length_of_interview\": 1250, \"qualified_conversion\": 41, \"num_quality_terminations\": 10}, \"qualifications\": [{\"id\": \"age\", \"response_ids\": [\"18\", \"19\", \"20\", \"21\", \"22\", \"23\", \"24\", \"25\", \"26\", \"27\", \"28\", \"29\", \"30\", \"31\", \"32\", \"33\", \"34\"]}, {\"id\": \"education\", \"response_ids\": [\"1\", \"10\", \"2\", \"3\", \"4\", \"5\", \"6\", \"7\"]}, {\"id\": \"gender\", \"response_ids\": [\"2\"]}], \"cost_per_interview\": 15}, {\"id\": \"2bec6858-5538-4351-b1ec-84ec527de8c0\", \"statistics\": {\"num_failures\": 0, \"num_timeouts\": 0, \"num_available\": 7, \"num_completes\": 1, \"num_qualified\": 7, \"num_in_progress\": 3, \"num_over_quotas\": 0, \"length_of_interview\": 1080, \"qualified_conversion\": 100, \"num_quality_terminations\": 3}, \"qualifications\": [{\"id\": \"age\", \"response_ids\": [\"35\", \"36\", \"37\", \"38\", \"39\", \"40\", \"41\", \"42\", \"43\", \"44\"]}, {\"id\": \"education\", \"response_ids\": [\"8\", \"9\"]}, {\"id\": \"gender\", \"response_ids\": [\"2\"]}], \"cost_per_interview\": 15}, {\"id\": \"32934928-2aa4-4ee9-8102-e64163aae2c4\", \"statistics\": {\"num_failures\": 0, \"num_timeouts\": 0, \"num_available\": 0, \"num_completes\": 5, \"num_qualified\": 18, \"num_in_progress\": 6, \"num_over_quotas\": 0, \"length_of_interview\": 1197, \"qualified_conversion\": 42, \"num_quality_terminations\": 7}, \"qualifications\": [{\"id\": \"age\", \"response_ids\": [\"35\", \"36\", \"37\", \"38\", \"39\", \"40\", \"41\", \"42\", \"43\", \"44\"]}, {\"id\": \"education\", \"response_ids\": [\"1\", \"10\", \"2\", \"3\", \"4\", \"5\", \"6\", \"7\"]}, {\"id\": \"gender\", \"response_ids\": [\"2\"]}], \"cost_per_interview\": 15}, {\"id\": \"edd6111c-f8b3-4471-849d-49fdef14c998\", \"statistics\": {\"num_failures\": 0, \"num_timeouts\": 0, \"num_available\": 12, \"num_completes\": 3, \"num_qualified\": 7, \"num_in_progress\": 2, \"num_over_quotas\": 0, \"length_of_interview\": 1080, \"qualified_conversion\": 100, \"num_quality_terminations\": 2}, \"qualifications\": [{\"id\": \"age\", \"response_ids\": [\"45\", \"46\", \"47\", \"48\", \"49\", \"50\", \"51\", \"52\", \"53\", \"54\", \"55\", \"56\", \"57\", \"58\", \"59\", \"60\", \"61\", \"62\", \"63\", \"64\"]}, {\"id\": \"education\", \"response_ids\": [\"8\", \"9\"]}, {\"id\": \"gender\", \"response_ids\": [\"2\"]}], \"cost_per_interview\": 15}, {\"id\": \"e592231f-59d9-47af-a8df-4130191235d7\", \"statistics\": {\"num_failures\": 0, \"num_timeouts\": 0, \"num_available\": 16, \"num_completes\": 6, \"num_qualified\": 17, \"num_in_progress\": 6, \"num_over_quotas\": 0, \"length_of_interview\": 1002, \"qualified_conversion\": 55, \"num_quality_terminations\": 5}, \"qualifications\": [{\"id\": \"age\", \"response_ids\": [\"45\", \"46\", \"47\", \"48\", \"49\", \"50\", \"51\", \"52\", \"53\", \"54\", \"55\", \"56\", \"57\", \"58\", \"59\", \"60\", \"61\", \"62\", \"63\", \"64\"]}, {\"id\": \"education\", \"response_ids\": [\"1\", \"10\", \"2\", \"3\", \"4\", \"5\", \"6\", \"7\"]}, {\"id\": \"gender\", \"response_ids\": [\"2\"]}], \"cost_per_interview\": 26}, {\"id\": \"4db03fdc-ec3b-434c-9546-00875ee62c70\", \"statistics\": {\"num_failures\": 0, \"num_timeouts\": 0, \"num_available\": 1, \"num_completes\": 0, \"num_qualified\": 1, \"num_in_progress\": 1, \"num_over_quotas\": 0, \"length_of_interview\": 1080, \"qualified_conversion\": 100, \"num_quality_terminations\": 0}, \"qualifications\": [{\"id\": \"age\", \"response_ids\": [\"100\", \"65\", \"66\", \"67\", \"68\", \"69\", \"70\", \"71\", \"72\", \"73\", \"74\", \"75\", \"76\", \"77\", \"78\", \"79\", \"80\", \"81\", \"82\", \"83\", \"84\", \"85\", \"86\", \"87\", \"88\", \"89\", \"90\", \"91\", \"92\", \"93\", \"94\", \"95\", \"96\", \"97\", \"98\", \"99\"]}, {\"id\": \"education\", \"response_ids\": [\"8\", \"9\"]}, {\"id\": \"gender\", \"response_ids\": [\"2\"]}], \"cost_per_interview\": 49}, {\"id\": \"abea9aff-4ae8-436b-ad6a-e56157266411\", \"statistics\": {\"num_failures\": 0, \"num_timeouts\": 0, \"num_available\": 1, \"num_completes\": 5, \"num_qualified\": 7, \"num_in_progress\": 1, \"num_over_quotas\": 0, \"length_of_interview\": 1395, \"qualified_conversion\": 83, \"num_quality_terminations\": 1}, \"qualifications\": [{\"id\": \"age\", \"response_ids\": [\"100\", \"65\", \"66\", \"67\", \"68\", \"69\", \"70\", \"71\", \"72\", \"73\", \"74\", \"75\", \"76\", \"77\", \"78\", \"79\", \"80\", \"81\", \"82\", \"83\", \"84\", \"85\", \"86\", \"87\", \"88\", \"89\", \"90\", \"91\", \"92\", \"93\", \"94\", \"95\", \"96\", \"97\", \"98\", \"99\"]}, {\"id\": \"education\", \"response_ids\": [\"1\", \"10\", \"2\", \"3\", \"4\", \"5\", \"6\", \"7\"]}, {\"id\": \"gender\", \"response_ids\": [\"2\"]}], \"cost_per_interview\": 118}, {\"id\": \"ee88b4c7-a31c-45a6-8710-e6020b902b8f\", \"statistics\": {\"num_failures\": 0, \"num_timeouts\": 0, \"num_available\": 7, \"num_completes\": 1, \"num_qualified\": 11, \"num_in_progress\": 1, \"num_over_quotas\": 0, \"length_of_interview\": 1080, \"qualified_conversion\": 100, \"num_quality_terminations\": 9}, \"qualifications\": [{\"id\": \"age\", \"response_ids\": [\"18\", \"19\", \"20\", \"21\", \"22\", \"23\", \"24\", \"25\", \"26\", \"27\", \"28\", \"29\", \"30\", \"31\", \"32\", \"33\", \"34\"]}, {\"id\": \"education\", \"response_ids\": [\"8\", \"9\"]}, {\"id\": \"gender\", \"response_ids\": [\"1\"]}], \"cost_per_interview\": 27}, {\"id\": \"fa0a9dbe-cee1-401a-83b6-5341f79a5de8\", \"statistics\": {\"num_failures\": 0, \"num_timeouts\": 0, \"num_available\": 16, \"num_completes\": 0, \"num_qualified\": 10, \"num_in_progress\": 3, \"num_over_quotas\": 0, \"length_of_interview\": 1080, \"qualified_conversion\": 100, \"num_quality_terminations\": 7}, \"qualifications\": [{\"id\": \"age\", \"response_ids\": [\"18\", \"19\", \"20\", \"21\", \"22\", \"23\", \"24\", \"25\", \"26\", \"27\", \"28\", \"29\", \"30\", \"31\", \"32\", \"33\", \"34\"]}, {\"id\": \"education\", \"response_ids\": [\"1\", \"10\", \"2\", \"3\", \"4\", \"5\", \"6\", \"7\"]}, {\"id\": \"gender\", \"response_ids\": [\"1\"]}], \"cost_per_interview\": 35}, {\"id\": \"4231e26f-f898-4ed4-adcd-4a12eceec4ff\", \"statistics\": {\"num_failures\": 0, \"num_timeouts\": 0, \"num_available\": 4, \"num_completes\": 2, \"num_qualified\": 11, \"num_in_progress\": 2, \"num_over_quotas\": 0, \"length_of_interview\": 1080, \"qualified_conversion\": 100, \"num_quality_terminations\": 7}, \"qualifications\": [{\"id\": \"age\", \"response_ids\": [\"35\", \"36\", \"37\", \"38\", \"39\", \"40\", \"41\", \"42\", \"43\", \"44\"]}, {\"id\": \"education\", \"response_ids\": [\"8\", \"9\"]}, {\"id\": \"gender\", \"response_ids\": [\"1\"]}], \"cost_per_interview\": 23}, {\"id\": \"09da93aa-4403-47ab-abd2-65d14ab30f78\", \"statistics\": {\"num_failures\": 0, \"num_timeouts\": 0, \"num_available\": 8, \"num_completes\": 4, \"num_qualified\": 13, \"num_in_progress\": 1, \"num_over_quotas\": 0, \"length_of_interview\": 1080, \"qualified_conversion\": 100, \"num_quality_terminations\": 8}, \"qualifications\": [{\"id\": \"age\", \"response_ids\": [\"35\", \"36\", \"37\", \"38\", \"39\", \"40\", \"41\", \"42\", \"43\", \"44\"]}, {\"id\": \"education\", \"response_ids\": [\"1\", \"10\", \"2\", \"3\", \"4\", \"5\", \"6\", \"7\"]}, {\"id\": \"gender\", \"response_ids\": [\"1\"]}], \"cost_per_interview\": 31}, {\"id\": \"fb585549-cf62-4d44-8cf9-552430f20381\", \"statistics\": {\"num_failures\": 0, \"num_timeouts\": 0, \"num_available\": 9, \"num_completes\": 2, \"num_qualified\": 12, \"num_in_progress\": 4, \"num_over_quotas\": 0, \"length_of_interview\": 1080, \"qualified_conversion\": 100, \"num_quality_terminations\": 6}, \"qualifications\": [{\"id\": \"age\", \"response_ids\": [\"45\", \"46\", \"47\", \"48\", \"49\", \"50\", \"51\", \"52\", \"53\", \"54\", \"55\", \"56\", \"57\", \"58\", \"59\", \"60\", \"61\", \"62\", \"63\", \"64\"]}, {\"id\": \"education\", \"response_ids\": [\"8\", \"9\"]}, {\"id\": \"gender\", \"response_ids\": [\"1\"]}], \"cost_per_interview\": 19}, {\"id\": \"6266628e-c881-4a4b-bb81-6203f3ee85e6\", \"statistics\": {\"num_failures\": 0, \"num_timeouts\": 0, \"num_available\": 0, \"num_completes\": 15, \"num_qualified\": 42, \"num_in_progress\": 14, \"num_over_quotas\": 0, \"length_of_interview\": 1349, \"qualified_conversion\": 54, \"num_quality_terminations\": 13}, \"qualifications\": [{\"id\": \"age\", \"response_ids\": [\"45\", \"46\", \"47\", \"48\", \"49\", \"50\", \"51\", \"52\", \"53\", \"54\", \"55\", \"56\", \"57\", \"58\", \"59\", \"60\", \"61\", \"62\", \"63\", \"64\"]}, {\"id\": \"education\", \"response_ids\": [\"1\", \"10\", \"2\", \"3\", \"4\", \"5\", \"6\", \"7\"]}, {\"id\": \"gender\", \"response_ids\": [\"1\"]}], \"cost_per_interview\": 41}, {\"id\": \"c4ef10f3-741d-463f-8366-3a174c134f8c\", \"statistics\": {\"num_failures\": 0, \"num_timeouts\": 0, \"num_available\": 2, \"num_completes\": 1, \"num_qualified\": 1, \"num_in_progress\": 0, \"num_over_quotas\": 0, \"length_of_interview\": 1080, \"qualified_conversion\": 100, \"num_quality_terminations\": 0}, \"qualifications\": [{\"id\": \"age\", \"response_ids\": [\"100\", \"65\", \"66\", \"67\", \"68\", \"69\", \"70\", \"71\", \"72\", \"73\", \"74\", \"75\", \"76\", \"77\", \"78\", \"79\", \"80\", \"81\", \"82\", \"83\", \"84\", \"85\", \"86\", \"87\", \"88\", \"89\", \"90\", \"91\", \"92\", \"93\", \"94\", \"95\", \"96\", \"97\", \"98\", \"99\"]}, {\"id\": \"education\", \"response_ids\": [\"8\", \"9\"]}, {\"id\": \"gender\", \"response_ids\": [\"1\"]}], \"cost_per_interview\": 52}, {\"id\": \"94838460-f297-4060-98c3-47cbadd84868\", \"statistics\": {\"num_failures\": 0, \"num_timeouts\": 0, \"num_available\": 0, \"num_completes\": 4, \"num_qualified\": 6, \"num_in_progress\": 2, \"num_over_quotas\": 0, \"length_of_interview\": 1080, \"qualified_conversion\": 100, \"num_quality_terminations\": 0}, \"qualifications\": [{\"id\": \"age\", \"response_ids\": [\"100\", \"65\", \"66\", \"67\", \"68\", \"69\", \"70\", \"71\", \"72\", \"73\", \"74\", \"75\", \"76\", \"77\", \"78\", \"79\", \"80\", \"81\", \"82\", \"83\", \"84\", \"85\", \"86\", \"87\", \"88\", \"89\", \"90\", \"91\", \"92\", \"93\", \"94\", \"95\", \"96\", \"97\", \"98\", \"99\"]}, {\"id\": \"education\", \"response_ids\": [\"1\", \"10\", \"2\", \"3\", \"4\", \"5\", \"6\", \"7\"]}, {\"id\": \"gender\", \"response_ids\": [\"1\"]}], \"cost_per_interview\": 128}]', 'active', '{\"num_entrants\": 234, \"num_failures\": 0, \"num_timeouts\": 0, \"num_available\": 88, \"num_completes\": 59, \"num_qualified\": 196, \"incidence_rate\": 100, \"num_screenouts\": 32, \"num_in_progress\": 61, \"num_over_quotas\": 3, \"system_conversion\": 34, \"earnings_per_click\": 16, \"length_of_interview\": 1218, \"qualified_conversion\": 43, \"num_quality_terminations\": 79}', 0, 'daily_tracker', 10800, 'general', '2023-05-03 09:32:56', '2023-05-03 09:32:56'); -- ///////////////////////// SELECT m.id, -- m.buyer_id, -- m.buyer_account_id, -- m.country_id, -- m.end_date, -- m.closed_at, -- m.exclusions, -- m.language_ids, -- m.NAME, -- m.published_at, quotas->"$[*].qualifications" -- m.state, -- m.statistics, -- m.supplier_exclusive, -- m.survey_type, -- m.timeout, -- m.topic_id, -- m.created, -- m.modified, -- c.quota_id -- c.num_available, -- c.qualification_id, -- c.response FROM bids AS m -- JSON_TABLE ( -- m.quotas, -- "$[*]" COLUMNS ( -- quota_id VARCHAR ( 64 ) PATH '$.id', -- num_failures INT ( 5 ) PATH '$.statistics.num_failures', -- num_timeouts INT PATH '$.statistics.num_timeouts', -- num_available INT PATH '$.statistics.num_available', -- num_completes INT PATH '$.statistics.num_completes', -- num_qualified INT PATH '$.statistics.num_qualified', -- num_in_progress INT PATH '$.statistics.num_in_progress', -- num_over_quotas INT PATH '$.statistics.num_over_quotas', -- length_of_interview INT PATH '$.statistics.length_of_interview', -- qualified_conversion INT PATH '$.statistics.qualified_conversion', -- num_quality_terminations INT PATH '$.statistics.num_quality_terminations', -- cost_per_interview INT ( 5 ) PATH '$.cost_per_interview', -- NESTED PATH '$.qualifications[*]' COLUMNS ( qualification_id VARCHAR ( 10 ) PATH '$.id', NESTED PATH '$.response_ids[*]' COLUMNS ( response INT ( 4 ) PATH '$' ) ) -- ) -- ) c WHERE ( m.country_id = 'es' AND m.state = 'active' AND m.survey_type = 'daily_tracker' ) AND JSON_CONTAINS( m.quotas->'$[*].qualifications', '{"id": "age", "response_ids": ["99"]}', '$' ) AND JSON_CONTAINS( m.quotas->'$[*].qualifications', '{"id": "gender", "response_ids": ["1"]}', '$' ) AND ( JSON_CONTAINS( m.quotas->'$[*].qualifications', '{"id": "education", "response_ids": ["4"]}', '$' ) -- OR -- JSON_CONTAINS( -- m.quotas->'$[*].qualifications', -- '{"id": "education", "response_ids": ["8"]}', -- '$' -- ) ) ;
Stuck with a problem? Got Error? Ask ChatGPT!
Copy Clear
© 2024 SQLize - format run and share SQL queries online | About project | Disclaimer | our telegram chat
VPS servers at the highest possible quality
PHP versions: PHP 5.6, PHP 7.4, PHP 8.0, PHP 8.1, PHP 8.2.13
RDBMS versions: MySQL 5.7, MySQL 5.7 Sakila (ReadOnly), MySQL 8.0, MySQL 8.0 Sakila (ReadOnly), MariaDB 10, MariaDB 10 Sakila (ReadOnly), SQLite 3, PostgreSQL 10 Bookings (ReadOnly), PostgreSQL 11, PostgreSQL 12, PostgreSQL 13, PostgreSQL 14, PostgreSQL 15, MS SQL Server 2017, MS SQL Server 2019, MS SQL Server 2022, Firebird 4.0, Firebird 4.0 (Employee), Oracle Database 19c (HR), Oracle Database 21c, Oracle Database 23c Free, SOQOL