SQLize | PHPize | SQLtest

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": ["33"]}', '$' ) AND JSON_CONTAINS( m.quotas->'$[*].qualifications', '{"id": "gender", "response_ids": ["1"]}', '$' ) AND JSON_CONTAINS( m.quotas->'$[*].qualifications', '{"id": "education", "response_ids": ["4"]}', '$' ) ;