Supporting JSON Arrays

[jamesdixon]

Me again :)

I came across an issue where inserting an array of JSON objects fails into Postgres fails. I tracked it down to Postgres not liking that the array is part of the string.

[
  { key: value },
  { key: value }
]

// stringified
"[{"key":"value"},{"key":"value"}]"

Postgres wants it like so:

[{"key":"value"},{"key":"value"}]

I can submit a PR to fix, but wasn't sure if this something that should only be done when using the Postgres client in Bookshelf.

Thoughts?

[jamesdixon]

Ok, apologies for the issue. It appears the whole reason to stringify the data beforehand is to avoid a design decision in node-pg that converts arrays to objects. This may end up being a KNEX issue, but just going explain it here just in case you've run into the issue. If not, let me know and I can move the issue elsewhere.

I'm attempting to insert an array of JSON objects into a JSONB column in postgres. The data looks like this after going through your plugin:

"[{"number":"(555) 555-1212","type":"home"},{"number":"(555) 555-1212","type":"mobile"},{"number":"(555) 555-1212","type":"work"}]"

I added the following to debug the query being run:

knex.on('query', function(data) { console.log(data) })

In my console, I see the following output:

Which results in the following error:

{
    "msec": 63700.382493019104,
    "error": "update \"user\" set \"address\" = $1, \"email\" = $2, \"first_name\" = $3, \"last_name\" = $4, \"phone_numbers\" = $5, \"secondary_contact_info\" = $6, \"updated_at\" = $7 where \"id\" = $8 - invalid input syntax for type json",
    "data": {
        "name": "error",
        "length": 195,
        "severity": "ERROR",
        "code": "22P02",
        "detail": "Expected \":\", but found \",\".",
        "where": "JSON data, line 1: ...\"number\\\":\\\"(555) 555-1212\\\",\\\"type\\\":\\\"home\\\"}\",...",
        "file": "json.c",
        "line": "1140",
        "routine": "report_parse_error",
        "isBoom": true,
        "isServer": true,
        "data": null,
        "output": {
            "statusCode": 500,
            "payload": {
                "statusCode": 500,
                "error": "Internal Server Error",
                "message": "An internal server error occurred"
            },
            "headers": {}
        }
    }
}

It appears Knex or possibly Bookshelf is adding a bunch of escape characters to the phoneNumbers string and removing the surrounding array that was output from your plugin.

Am i missing something here?

Thanks again.

[ricardogama]

Hey @jamesdixon, sorry for the delay but I've been on vacation. It seems that the values are being stringified more than once, and/or not being parsed after saving. So I guess the saved hook from the plugin is not being executed or you are stringifying and not parse back elsewhere in your code. Did you solve the problem?

[jamesdixon]

@ricardogama no worries at all! Yes, I tracked the issue back to the array being stringified twice. Thanks for the response. Happy New Year!

[ricardogama]

Nice to know you worked it out, James. Thanks and likewise!

[jamesdixon]

@ricardogama I know this is an old issue, but I'm running into the same issue with JSON arrays. Was curious if you had any problems with this?

[jamesdixon]

This appears to be a Bookshelf issue. If I use Knex to insert a JSON array, things work fine. Somewhere in the Bookshelf code, it appears that is stringifying the array again. Given that you are a contributor to the Bookshelf project, do you have any idea where this might be happening?

[ricardogama]

@jamesdixon Hey, sorry for the delay. I'll take a look into this, can you tell what client and bookshelf & knex versions you're using? I'll take a look into this.

[jamesdixon]

@ricardogama no worries!

I'm using Bookshelf v0.10 and Knex v0.11.9.

I opened an issue in the Bookshelf repo as I'm fairly certain that's where the problem resides.

[ricardogama]

@jamesdixon I'm tracking the issue, but what client are you using and what version, PostgreSQL?

[jamesdixon]

Postgres 9.5

[jamesdixon]

@ricardogama were you ever able to reproduce this issue?

[ricardogama]

@jamesdixon Sorry for the delay. If you're using save with the patch option that might be the problem, I'll investigate.

[ricardogama]

@jamesdixon Can you try the feature/update-with-patch-option and check if this issue get fixed?

[jamesdixon]

@ricardogama I'll give it a shot. Thank you!