{"action":"created","issue":{"url":"https://api.github.com/repos/tgriesser/bookshelf/issues/1328","repository_url":"https://api.github.com/repos/tgriesser/bookshelf","labels_url":"https://api.github.com/repos/tgriesser/bookshelf/issues/1328/labels{/name}","comments_url":"https://api.github.com/repos/tgriesser/bookshelf/issues/1328/comments","events_url":"https://api.github.com/repos/tgriesser/bookshelf/issues/1328/events","html_url":"https://github.com/tgriesser/bookshelf/issues/1328","id":167493366,"number":1328,"title":"Inserting JSON Array produces error","user":{"login":"jamesdixon","id":110114,"avatar_url":"https://avatars.githubusercontent.com/u/110114?v=3","gravatar_id":"","url":"https://api.github.com/users/jamesdixon","html_url":"https://github.com/jamesdixon","followers_url":"https://api.github.com/users/jamesdixon/followers","following_url":"https://api.github.com/users/jamesdixon/following{/other_user}","gists_url":"https://api.github.com/users/jamesdixon/gists{/gist_id}","starred_url":"https://api.github.com/users/jamesdixon/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/jamesdixon/subscriptions","organizations_url":"https://api.github.com/users/jamesdixon/orgs","repos_url":"https://api.github.com/users/jamesdixon/repos","events_url":"https://api.github.com/users/jamesdixon/events{/privacy}","received_events_url":"https://api.github.com/users/jamesdixon/received_events","type":"User","site_admin":false},"labels":[{"url":"https://api.github.com/repos/tgriesser/bookshelf/labels/question","name":"question","color":"cc317c"}],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":4,"created_at":"2016-07-26T00:06:06Z","updated_at":"2016-08-26T09:39:48Z","closed_at":null,"body":"Hi all,\r\n\r\nI've already looked at #94 and #864 and still cannot get this to work. I've also tried @ricardogama's bookshelf-json-columns plugin with no luck.\r\n\r\nAs other's have described, the issue is that an error is thrown when inserting a JSON Array into a JSONB column in Postgres. Inserting a JSON object works fine, just not an array of JSON objects. There is a known issue that you must stringify the input before passing it through node-pg -- this is what the bookshelf-json-columns plugin does. Unfortunately, when an array of these objects is passed, it appears that Bookshelf is stringifying the input multiple times. Here's an error when trying to insert a JSON array into a JSONB column:\r\n\r\n```js\r\n[\r\n   { serviceId: 2, petId: 5, checked: true },\r\n   { serviceId: 3, petId: null, checked: false }\r\n]\r\n```\r\n\r\nAfter stringifying: \r\n\r\n```js\r\n\"[{\"serviceId\":2,\"petId\":5,\"checked\":true},{\"serviceId\":3,\"petId\":null,\"checked\":false}]\"\r\n```\r\n\r\nInsert error:\r\n\r\n```\r\n{\"msec\":54.03032702207565,\r\n\"error\":\"update \\\"appointment\\\" set \\\"ended_at\\\" = $1, \\\"report_card\\\" = $2, \\\"staff_notes\\\" = $3, \\\"status\\\" = $4, \\\"updated_at\\\" = $5 where \\\"id\\\" = $6 - invalid input syntax for type json\",\"data\":{\"message\":\"update \\\"appointment\\\" set \\\"ended_at\\\" = $1, \\\"report_card\\\" = $2, \\\"staff_notes\\\" = $3, \\\"status\\\" = $4, \\\"updated_at\\\" = $5 where \\\"id\\\" = $6 - invalid input syntax for type json\",\r\n\"severity\":\"ERROR\",\r\n\"code\":\"22P02\",\r\n\"condition\":\"invalid_text_representation\",\r\n\"detail\":\"Expected \\\":\\\", but found \\\",\\\".\",\"where\":\"JSON data, line 1: {\\\"{\\\\\\\"serviceId\\\\\\\":2,\\\\\\\"petId\\\\\\\":5,\\\\\\\"checked\\\\\\\":true}\\\",...\",\"file\":\"json.c\",\"line\":1198,\"routine\":\"report_parse_error\",\"name\":\"PgError\",\"isBoom\":true,\"isServer\":true,\"data\":null,\"output\":{\"statusCode\":500,\"payload\":{\"statusCode\":500,\"error\":\"Internal Server Error\",\"message\":\"An internal server error occurred\"},\"headers\":{}}}}\r\n```\r\n\r\nYou'll notice that the output detailed in the error message contains a number of extra slashes, which appear to be caused by stringifying the input multiple times.\r\n\r\nHowever, if I do the following in Knex, I get no errors, which leads me to believe this is a Bookshelf issue:\r\n\r\n```js\r\n    return knex.table('appointment').insert({\r\n        business_id: businessId,\r\n        staff_id: staffId,\r\n        customer_id: customerId,\r\n        report_card: JSON.stringify([\r\n            { serviceName: 'Poop', 'petName': 'JJ', checked: true },\r\n            { serviceName: 'Pee', 'petName': 'Mario', checked: true },\r\n            { serviceName: 'Poop', 'petName': 'JJ', checked: true },\r\n            { serviceName: 'Pee', 'petName': 'Mario', checked: true }\r\n        ])\r\n    });\r\n```\r\n\r\nI started digging through the Bookshelf code to find out where this is happening, but someone who knows the codebase better may be able to expedite things.\r\n\r\nThanks,\r\nJames"},"comment":{"url":"https://api.github.com/repos/tgriesser/bookshelf/issues/comments/242681715","html_url":"https://github.com/tgriesser/bookshelf/issues/1328#issuecomment-242681715","issue_url":"https://api.github.com/repos/tgriesser/bookshelf/issues/1328","id":242681715,"user":{"login":"dpiccone","id":1982461,"avatar_url":"https://avatars.githubusercontent.com/u/1982461?v=3","gravatar_id":"","url":"https://api.github.com/users/dpiccone","html_url":"https://github.com/dpiccone","followers_url":"https://api.github.com/users/dpiccone/followers","following_url":"https://api.github.com/users/dpiccone/following{/other_user}","gists_url":"https://api.github.com/users/dpiccone/gists{/gist_id}","starred_url":"https://api.github.com/users/dpiccone/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/dpiccone/subscriptions","organizations_url":"https://api.github.com/users/dpiccone/orgs","repos_url":"https://api.github.com/users/dpiccone/repos","events_url":"https://api.github.com/users/dpiccone/events{/privacy}","received_events_url":"https://api.github.com/users/dpiccone/received_events","type":"User","site_admin":false},"created_at":"2016-08-26T09:39:48Z","updated_at":"2016-08-26T09:39:48Z","body":"Hi guys, I have the same issue here, using bookshelf 0.10 and Postgres 9.5\r\nA simple schema where `profile_parts` is a JSON column, saving this values in there \r\n\r\n```\r\nprofileParts: [ { type: 'PROFILE', value: { link: 'https://foo.bar', image: testImage.id } } ]\r\n```\r\n\r\nproduces this query \r\n\r\n```\r\n{ method: 'insert',\r\n  options: {},\r\n  timeout: false,\r\n  cancelOnTimeout: false,\r\n  bindings: \r\n   [ '2016-08-26T11:23:12.485+02:00',\r\n     'USD',\r\n     'XXXXX',\r\n     '{\"{\\\\\"type\\\\\":\\\\\"PROFILE\\\\\",\\\\\"value\\\\\":{\\\\\"link\\\\\":\\\\\"https://foo.bar\\\\\",\\\\\"image\\\\\":\\\\\"70d0d7e0-e0ca-45b5-be48-252d9623b9e8\\\\\"}}\"}',\r\n     '123',\r\n     '2016-08-26T11:23:12.485+02:00',\r\n     'XXXX1',\r\n     'XXXX' ],\r\n  __knexQueryUid: '70b1c370-861f-46eb-b943-9ca318777d72',\r\n  sql: 'insert into \"vendors\" (\"created_at\", \"currency\", \"name\", \"profile_parts\", \"rate\", \"updated_at\", \"user_id\", \"verification_state\") values (?, ?, ?, ?, ?, ?, ?, ?) returning \"id\"',\r\n  returning: 'id' }\r\n```\r\n\r\nThe value is not just escaped but the wrapping `[...]` are replaced with `{...}`\r\n\r\nand I got the same error `invalid input syntax for type json'`\r\n\r\nI am not using any plugin as referenced in https://github.com/seegno/bookshelf-json-columns/issues/7, just plain bookshelf"}}
{"actor":{"display_login":"dpiccone"}}