JSON Support

[drusellers]

I am currently trying to get NHibernate to work with JSON data type. I am currently getting stuck on ERROR: 42804: column "post" is of type json but expression is of type text - I have used Npgsql directly and it works just fine. I am thinking that step one might be to add Json to NpgsqlDbType but after that I am not 100% sure where to go.

[drusellers]

Ok, I traced it down to 'UseCast'. NHibernate is setting the NpgsqlParameter in some fashion that is turning UseCast = true and is setting it to text (as it has been instructed to do). What I would love here is a way to tell it that NpgsqlDbType is Json and I wonder if that wouldn't fix my problems right up.

[drusellers]

I have worked around the issue with the following horrible code.

var parameter = cmd.Parameters[index];
parameter.Value = value;

var field = typeof (NpgsqlParameter).GetField("useCast", BindingFlags.NonPublic | BindingFlags.Instance);
field.SetValue(parameter, false);

[franciscojunior]

Hi, @drusellers !

Indeed, Npgsql needs a better way to handle type casting. I even created an issue about that: #125 .

In order to add json datatype support, besides adding NpgsqlDbType, you will need to add a line to PrepareDefaultTypesMap method in NpgsqlTypesHelper:

nativeTypeMapping.AddType("json", NpgsqlDbType.Json, DbType.Object, true);

If you want to provide a custom converter in text format and/or binary format, you can do so by specifying your custom converters when calling AddType. You may have a look at current converters like:

nativeTypeMapping.AddType("float4", NpgsqlDbType.Real, DbType.Single, false,
                                            BasicNativeToBackendTypeConverter.SingleDoubleToFloat4Float8Text,
                                            BasicNativeToBackendTypeConverter.SingleToFloat4Binary);

We can work together on this issue to add it.

[drusellers]

I would definitely appreciate any help you can provide on the topic.

[franciscojunior]

Sure! I'll play with it and will let you know what I get.

I'll use this issue to create documentation about how to add custom types support to Npgsql. I think this will help a lot of developers who work with Npgsql. :)

[drusellers]

@franciscojunior what's the best way for me to help?

[franciscojunior]

Hi, @drusellers ! I'm really sorry I didn't check this issue yet. :(
I was working with some other issues related to Npgsql releases.

As things seem to be going ok now, I can work on this issue too. Give me some time while I write some documentation about how to add a custom type. I'll use json type as the example.

Sorry for making you wait :(

[drusellers]

No worries. I have a work around. Just not sure how best to help. -d

On Sat, Mar 29, 2014 at 5:29 PM, Francisco Figueiredo Jr.
notifications@github.com wrote:

Hi, @drusellers ! I'm really sorry I didn't check this issue yet. :(
I was working with some other issues related to Npgsql releases.
As things seem to be going ok now, I can work on this issue too. Give me some time while I write some documentation about how to add a custom type. I'll use json type as the example.

Sorry for making you wait :(

Reply to this email directly or view it on GitHub:
#168 (comment)

[franciscojunior]

Great! I'm glad to know I'm not giving you too much trouble :) Thanks for your comprehension.

[squidge]

Hello,

I have been getting the same issue "ERROR: 42804: column "x" is of type json but expression is of type text"

Is this a Npgsql issue or OrmLite which is what I am using in this case.

Many thanks

[drusellers]

I believe it to ultimately be a 'Npgsql' issue - because its trying to do a cast in the sql to 'text' when the column is 'json' - in order to get past it I had to do the following 'hack' to make it work, for more details see above.

var parameter = cmd.Parameters[index];
parameter.Value = value;

var field = typeof (NpgsqlParameter).GetField("useCast", BindingFlags.NonPublic | BindingFlags.Instance);
field.SetValue(parameter, false);

[squidge]

I see, I'll try your hack then while a fix is put in place..

Many thanks

[squidge]

@drusellers so where exactly did you put this code?

[drusellers]

I don't know OrmLite enough to say where - I did mine with NHibernate and put it in an IUserType - but the gist is you'll need to get access to the NpgsqlParameter that is handling the JSON column type.

[squidge]

I found the place where to put it in OrmLite and it works. I also found out you can do the same in Npgsql and "turn off" casting but the hack on the ORM is a better place I would think.

Thanks for your help

[franciscojunior]

Hi, all!
I'm working to add support for json datatype. I'll also use this opportunity to add documentation to Npgsql about how to add datatypes so others can be easily added in the future.

Sorry for taking so long. I hope to get something about it by the weekend.

[franciscojunior]

Just created a pull request which adds support for json datatype.

@drusellers , you will now be able to set the NpgsqlDbType to Json.

For while, the only way to get json support is to set the NpgsqlDbType. As json datatype looks just like string, Npgsql won't be able to tell the difference between a text and json if you simply set the parameter value.

Regarding your changes to omit the parameter cast (which is also another possible solution) I'm working on this support too. See #146 and #125

[franciscojunior]

I also added a wiki page which explains how to add custom datatypes to Npgsql. It is very basic at this moment, but I'll fill it with more details. Please, check it out and let me know what you think.
https://github.com/npgsql/Npgsql/wiki/How-to-add-a-custom-type-to-Npgsql

[drusellers]

👍 AWESOME

On Thu, Apr 17, 2014 at 4:14 PM, Francisco Figueiredo Jr. <
notifications@github.com> wrote:

I also added a wiki page which explains how to add custom datatypes to
Npgsql. It is very basic at this moment, but I'll fill it with more
details. Please, check it out and let me know what you think.
https://github.com/npgsql/Npgsql/wiki/How-to-add-a-custom-type-to-Npgsql

—
Reply to this email directly or view it on GitHubhttps://github.com//issues/168#issuecomment-40763712
.

[franciscojunior]

@drusellers , we added support for json and jsonb and hstore as well. See #242 (comment) for more information.

Would you mind to check latest master branch and test if it is working for you now?

Thanks in advance.

[roji]

I'm going to go ahead and close this, if any issue is found with JSON support please reopen.

[drusellers]

👍 everything looks good