{"action":"created","issue":{"url":"https://api.github.com/repos/boldtrn/JsonbBundle/issues/5","labels_url":"https://api.github.com/repos/boldtrn/JsonbBundle/issues/5/labels{/name}","comments_url":"https://api.github.com/repos/boldtrn/JsonbBundle/issues/5/comments","events_url":"https://api.github.com/repos/boldtrn/JsonbBundle/issues/5/events","html_url":"https://github.com/boldtrn/JsonbBundle/pull/5","id":112836160,"number":5,"title":"Do not translate falsy values to sql NULL.","user":{"login":"d-ph","id":4347095,"avatar_url":"https://avatars.githubusercontent.com/u/4347095?v=3","gravatar_id":"","url":"https://api.github.com/users/d-ph","html_url":"https://github.com/d-ph","followers_url":"https://api.github.com/users/d-ph/followers","following_url":"https://api.github.com/users/d-ph/following{/other_user}","gists_url":"https://api.github.com/users/d-ph/gists{/gist_id}","starred_url":"https://api.github.com/users/d-ph/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/d-ph/subscriptions","organizations_url":"https://api.github.com/users/d-ph/orgs","repos_url":"https://api.github.com/users/d-ph/repos","events_url":"https://api.github.com/users/d-ph/events{/privacy}","received_events_url":"https://api.github.com/users/d-ph/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":1,"created_at":"2015-10-22T16:03:19Z","updated_at":"2015-10-22T19:01:19Z","closed_at":null,"pull_request":{"url":"https://api.github.com/repos/boldtrn/JsonbBundle/pulls/5","html_url":"https://github.com/boldtrn/JsonbBundle/pull/5","diff_url":"https://github.com/boldtrn/JsonbBundle/pull/5.diff","patch_url":"https://github.com/boldtrn/JsonbBundle/pull/5.patch"},"body":"Hello,\r\n\r\nCan we talk about the reason you decided to translate empty arrays to sql NULLs in b69017a03d290b9edc3a76ccc03e9e2a568895ee?\r\n\r\nI know that Doctrine translates sql NULLs to empty arrays during hydration, but I think it's desirable to actually store \"[]\" instead of NULLs in the db for raw SQL reasons.\r\n\r\nE.g. this:\r\n```sql\r\nSELECT \r\n  jsonb_array_length(col1)\r\nFROM ( VALUES \r\n    ('[1, 2, 3]'::JSONB),\r\n    ('[4, 5, 6]'::JSONB),\r\n    ('[7, 8]'::JSONB),\r\n    ('[9, 10, 11, 14]'::JSONB),\r\n    (NULL::JSONB)\r\n  ) AS \"table\"(col1)\r\n;\r\n```\r\nwill return this:\r\n```\r\n3, 3, 2, 4, NULL\r\n```\r\nI would expect to get\r\n```\r\n3, 3, 2, 4, 0\r\n```\r\nBut this is just how NULLs are handled in SQL.\r\n\r\nI can see how this could cause issues for me. E.g. if I want to find a record, which has a `string[]` field with a length of 0, then this wouldn't work:\r\n```sql\r\nSELECT * FROM \"user\" WHERE jsonb_array_length(roles) = 0;\r\n```\r\nI would need to remember that I need to run this instead:\r\n```sql\r\nSELECT * FROM \"user\" WHERE jsonb_array_length(roles) IS NULL;\r\n// or\r\nSELECT * FROM \"user\" WHERE roles IS NULL;\r\n```\r\nThings get even dirtier if we think of aggregate functions here:\r\n```sql\r\nSELECT\r\n  jsonb_array_length(col1) AS first_column, COUNT(*)\r\nFROM ( VALUES\r\n    ('[1, 2, 3]'::JSONB),\r\n    ('[4, 5, 6]'::JSONB),\r\n    ('[7, 8]'::JSONB),\r\n    ('[9, 10, 11, 14]'::JSONB),\r\n    (NULL::JSONB)\r\n  ) AS \"table\"(col1)\r\nGROUP BY (first_column);\r\n```\r\nProduces:\r\n```\r\nNULL,1\r\n4,1\r\n2,1\r\n3,2\r\n```\r\nInstead of:\r\n```\r\n0,1\r\n4,1\r\n2,1\r\n3,2\r\n```\r\nDealing with NULLs instead of '[]' is just too much hassle. I know it's cheaper to store NULLs than '[]', but in my opinion the memory optimization is not worth the burden of maintenance (remembering, that we can get NULLs in places, when we'd expect integers).\r\n\r\nWhat do you think about reverting your commit? "},"comment":{"url":"https://api.github.com/repos/boldtrn/JsonbBundle/issues/comments/150323531","html_url":"https://github.com/boldtrn/JsonbBundle/pull/5#issuecomment-150323531","issue_url":"https://api.github.com/repos/boldtrn/JsonbBundle/issues/5","id":150323531,"user":{"login":"boldtrn","id":1553525,"avatar_url":"https://avatars.githubusercontent.com/u/1553525?v=3","gravatar_id":"","url":"https://api.github.com/users/boldtrn","html_url":"https://github.com/boldtrn","followers_url":"https://api.github.com/users/boldtrn/followers","following_url":"https://api.github.com/users/boldtrn/following{/other_user}","gists_url":"https://api.github.com/users/boldtrn/gists{/gist_id}","starred_url":"https://api.github.com/users/boldtrn/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/boldtrn/subscriptions","organizations_url":"https://api.github.com/users/boldtrn/orgs","repos_url":"https://api.github.com/users/boldtrn/repos","events_url":"https://api.github.com/users/boldtrn/events{/privacy}","received_events_url":"https://api.github.com/users/boldtrn/received_events","type":"User","site_admin":false},"created_at":"2015-10-22T19:01:19Z","updated_at":"2015-10-22T19:01:19Z","body":"Hey,\r\n\r\nright now I have the feeling that you are right. \r\n\r\nI will merge this to the master branch and write integration tests for it and see how it behaves. If I see any reason why this should be added again I will let you know with a decent reason."}}