{"action":"created","issue":{"url":"https://api.github.com/repos/zombodb/zombodb/issues/209","repository_url":"https://api.github.com/repos/zombodb/zombodb","labels_url":"https://api.github.com/repos/zombodb/zombodb/issues/209/labels{/name}","comments_url":"https://api.github.com/repos/zombodb/zombodb/issues/209/comments","events_url":"https://api.github.com/repos/zombodb/zombodb/issues/209/events","html_url":"https://github.com/zombodb/zombodb/issues/209","id":233911465,"number":209,"title":"zdb_tally not filtering by stem when using a shadow index and json_agg field","user":{"login":"MarkMatte","id":15038790,"avatar_url":"https://avatars3.githubusercontent.com/u/15038790?v=4","gravatar_id":"","url":"https://api.github.com/users/MarkMatte","html_url":"https://github.com/MarkMatte","followers_url":"https://api.github.com/users/MarkMatte/followers","following_url":"https://api.github.com/users/MarkMatte/following{/other_user}","gists_url":"https://api.github.com/users/MarkMatte/gists{/gist_id}","starred_url":"https://api.github.com/users/MarkMatte/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/MarkMatte/subscriptions","organizations_url":"https://api.github.com/users/MarkMatte/orgs","repos_url":"https://api.github.com/users/MarkMatte/repos","events_url":"https://api.github.com/users/MarkMatte/events{/privacy}","received_events_url":"https://api.github.com/users/MarkMatte/received_events","type":"User","site_admin":false},"labels":[{"id":237486354,"url":"https://api.github.com/repos/zombodb/zombodb/labels/bug","name":"bug","color":"fc2929","default":true},{"id":637777221,"url":"https://api.github.com/repos/zombodb/zombodb/labels/v3.1.13","name":"v3.1.13","color":"1d76db","default":false}],"state":"open","locked":false,"assignee":{"login":"eeeebbbbrrrr","id":5117238,"avatar_url":"https://avatars3.githubusercontent.com/u/5117238?v=4","gravatar_id":"","url":"https://api.github.com/users/eeeebbbbrrrr","html_url":"https://github.com/eeeebbbbrrrr","followers_url":"https://api.github.com/users/eeeebbbbrrrr/followers","following_url":"https://api.github.com/users/eeeebbbbrrrr/following{/other_user}","gists_url":"https://api.github.com/users/eeeebbbbrrrr/gists{/gist_id}","starred_url":"https://api.github.com/users/eeeebbbbrrrr/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/eeeebbbbrrrr/subscriptions","organizations_url":"https://api.github.com/users/eeeebbbbrrrr/orgs","repos_url":"https://api.github.com/users/eeeebbbbrrrr/repos","events_url":"https://api.github.com/users/eeeebbbbrrrr/events{/privacy}","received_events_url":"https://api.github.com/users/eeeebbbbrrrr/received_events","type":"User","site_admin":false},"assignees":[{"login":"eeeebbbbrrrr","id":5117238,"avatar_url":"https://avatars3.githubusercontent.com/u/5117238?v=4","gravatar_id":"","url":"https://api.github.com/users/eeeebbbbrrrr","html_url":"https://github.com/eeeebbbbrrrr","followers_url":"https://api.github.com/users/eeeebbbbrrrr/followers","following_url":"https://api.github.com/users/eeeebbbbrrrr/following{/other_user}","gists_url":"https://api.github.com/users/eeeebbbbrrrr/gists{/gist_id}","starred_url":"https://api.github.com/users/eeeebbbbrrrr/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/eeeebbbbrrrr/subscriptions","organizations_url":"https://api.github.com/users/eeeebbbbrrrr/orgs","repos_url":"https://api.github.com/users/eeeebbbbrrrr/repos","events_url":"https://api.github.com/users/eeeebbbbrrrr/events{/privacy}","received_events_url":"https://api.github.com/users/eeeebbbbrrrr/received_events","type":"User","site_admin":false}],"milestone":null,"comments":6,"created_at":"2017-06-06T14:30:17Z","updated_at":"2017-08-21T15:53:26Z","closed_at":null,"body":"zdb_tally not filtering by stem when using a shadow index and json_agg field unless we remove the alias in the fieldname variable.\r\n\r\n```\r\n--create case table and populate\r\nCREATE TABLE unit_tests.case_name\r\n(\r\npk_cpm bigint NOT NULL,\r\n\tcpm_name text,\r\n\tCONSTRAINT idx_unit_tests_case_name PRIMARY KEY (pk_cpm)\r\n);\r\n\r\nCREATE INDEX es_unit_tests_case_name ON unit_tests.case_name USING zombodb (zdb('unit_tests.case_name'::regclass, ctid), zdb(case_name.*)) WITH (url='http://###.##.##.##:1111/', shards='3', replicas='1');\r\n\r\nALTER INDEX unit_tests.es_unit_tests_data set (options='pk_data = <var.es_unit_tests_var>pk_var,pk_data = <vol.es_unit_tests_vol>pk_vol,data_bigint_array_2=<case_name.es_unit_tests_case_name>pk_cpm');\r\n\r\nINSERT INTO unit_tests.case_name(pk_cpm,cpm_name)\r\nvalues(1,'Smith, Bob'),(2,'Smith, Mary'),(3,'Jones, Mike'),(4,'Jones, Harry'),(5,'Jones, Sally'),(6,'Jones, UhOh')\r\n\r\nALTER INDEX unit_tests.es_unit_tests_case_name SET (always_resolve_joins = true);\r\nSELECT zdb_update_mapping('unit_tests.case_name'); \r\n\r\n--create shadow index and view\r\nCREATE OR REPLACE FUNCTION zdb_data_to_case(table_name regclass,ctid tid)\r\n\tRETURNS tid AS '$libdir/plugins/zombodb', 'zdb_table_ref_and_tid'\r\n\tLANGUAGE c IMMUTABLE STRICT\r\n\tCOST 1;\r\n\t\r\nALTER FUNCTION zdb_data_to_case(regclass, tid)\r\n\tOWNER TO cvuser;\r\n\t\r\nCREATE INDEX es_idx_data_to_case_shadow ON unit_tests.data USING zombodb (zdb_data_to_case('unit_tests.data', ctid), zdb(data.*)) \r\nWITH (shadow='unit_tests.es_unit_tests_data', options='case_data:(data_bigint_array_2=<case_name.es_unit_tests_case_name>fk_jur_to_cpm)');\r\n\r\nCREATE OR REPLACE VIEW unit_tests.data_json_agg_view AS \r\n select data.*,\r\n\t\t( SELECT json_agg(row_to_json(cpm.*)) AS json_agg FROM \r\n\t\t\t( SELECT case_name.* \r\n\t\t\tFROM unit_tests.case_name WHERE case_name.pk_cpm = ANY(data.data_bigint_array_2)) cpm) AS case_data,\r\n    zdb_data_to_case('unit_tests.data'::regclass, data.ctid) AS zdb\r\n   FROM unit_tests.data;\t\t   \r\n\r\n--incorrect results\r\nzdbtaptests=# SELECT upper(term) term, count\r\nFROM zdb_tally('unit_tests.data_json_agg_view', 'case_data.cpm_name', '0', '^jo.*', '', 5000, 'term'::zdb_tally_order);\r\n     term     | count\r\n--------------+-------\r\n JONES, HARRY |     1\r\n JONES, MIKE  |     1\r\n JONES, SALLY |     1\r\n JONES, UHOH  |     1\r\n SMITH, BOB   |     1\r\n SMITH, MARY  |     1\r\n(6 rows)\r\n\r\n--Correct results if we remove the fully qualified alias in the fieldname position\r\nzdbtaptests=# SELECT upper(term) term, count\r\nFROM zdb_tally('unit_tests.data_json_agg_view', 'cpm_name', '0', '^jo.*', '', 5000, 'term'::zdb_tally_order);\r\n     term     | count\r\n--------------+-------\r\n JONES, HARRY |     1\r\n JONES, MIKE  |     1\r\n JONES, SALLY |     1\r\n JONES, UHOH  |     1\r\n(4 rows)\r\n\r\nTime: 8.525 ms\r\n```\r\n"},"comment":{"url":"https://api.github.com/repos/zombodb/zombodb/issues/comments/323782412","html_url":"https://github.com/zombodb/zombodb/issues/209#issuecomment-323782412","issue_url":"https://api.github.com/repos/zombodb/zombodb/issues/209","id":323782412,"user":{"login":"eeeebbbbrrrr","id":5117238,"avatar_url":"https://avatars3.githubusercontent.com/u/5117238?v=4","gravatar_id":"","url":"https://api.github.com/users/eeeebbbbrrrr","html_url":"https://github.com/eeeebbbbrrrr","followers_url":"https://api.github.com/users/eeeebbbbrrrr/followers","following_url":"https://api.github.com/users/eeeebbbbrrrr/following{/other_user}","gists_url":"https://api.github.com/users/eeeebbbbrrrr/gists{/gist_id}","starred_url":"https://api.github.com/users/eeeebbbbrrrr/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/eeeebbbbrrrr/subscriptions","organizations_url":"https://api.github.com/users/eeeebbbbrrrr/orgs","repos_url":"https://api.github.com/users/eeeebbbbrrrr/repos","events_url":"https://api.github.com/users/eeeebbbbrrrr/events{/privacy}","received_events_url":"https://api.github.com/users/eeeebbbbrrrr/received_events","type":"User","site_admin":false},"created_at":"2017-08-21T15:53:26Z","updated_at":"2017-08-21T15:53:26Z","body":"reopening..."}}
{"actor":{"display_login":"eeeebbbbrrrr"}}