Insert JSONB data into Postgres (CFQUERYPARAM) - SOLVED

dev support
1

Hey guys,

We curently are storing JSON data (logs) in a text field in our database. Due to some new requirements, we would love to use the JSONB data type instead. Doing some testing we had everything working using a standard INSERT:

INSERT into table ( jsonb_data )
VALUES ( '#( serializeJSON( request.data ) )#' )

This works great, but when we moved to CFQUERYPARAM, we received an error that the data type of VARCHAR was invalid for JSONB. While we understand JSONB is stored as a binary, when you insert it, it is actually just text.

Lucee: 5.3.9.133
PostgreSQL Driver: 42.2.20

QUESTION: What data type should we be using when using CFQUERYPARAM?

As always, thanks in advance :grinning:

2

@wwilliams Please always specify your lucee version and installed PostgreSQL extension version

3

Lucee: 5.3.9.133
PostgreSQL Driver: 42.2.20

4

@wwilliams If you try to insert data using sqltype -varchar then you must need to insert value data based upon the varchar length. otherwise it causes an error or please try to the sqltype - text

5

Thanks for replying. I tried using cfsqltype = “text” and I got the following error:

ERROR: column “test_data” is of type jsonb but expression is of type character varying Hint: You will need to rewrite or cast the expression.

Thoughts?

6

Side note: I also have tried changing the column to a JSON type and tried numerous cfsqltype options – all fail with an error similar to:

ERROR: column “test_data” is of type json but expression is of type character varying Hint: You will need to rewrite or cast the expression.

7

I’ll try later

8

<cfquery name="tq"> insert into test_table(jdata)values( json_build_object( 'data', <cfqueryparam value = "#reqest.data#" cfsqltype="cf_sql_varchar"> )); </cfquery>

Try json_build_object() postgresql function.

9

Thanks Mark. That did allow us to add the JSON into the database using a cfqueryparam. Now the issue is that it causes the data to lose the JSONB functionality b/c json_build_object creates a key : value pair. Using your example, all that is stored is:

“data”: “{JSON DATA STRINGIFIED}”

I since have tried a number of other functions and I still have ended up sticking with the old faithful TEXT data type for the original field.

10

That was just an example query but I’m not sure how you’re saving the data, isn’t json/jsonb basically
“key”:“value” ?

11

So the data we are storing is complicated (compared to key / value). The data is about 5 levels deep and has structs and arrays. Based on what we are seeing, the only way to handle this is without cfqueryparam and we have decided to just stay with the TEXT column for now.

If someone has a solution, let us know. :hand_with_index_finger_and_thumb_crossed:

12

With

  • Driver: 42.2.20
  • PostgresSQL: 13.6
  • Lucee: 5.3.9.160

I do this:

  INSERT INTO tbjson ( field )
  VALUES (
      <cfqueryparam value="#SerializeJSON( jsonRaw )#" cfsqltype="varchar">::jsonb
  )

Yes, with “::jsonb” casting method.

Remember that when you take the data you have to do: field.toString()

HTH

1 Like
13

Worked like a charm. You’re the man - thanks!

2 Likes
14

Just to throw one more option in here, I believe that we use CF_SQL_OTHER / OTHER with queryparams for jsonb.

15

That is awesome! I didn’t know there was an “other” cfsqltype option. I typically use cfdocs.com for ref and this is the first time I have ever come across something that is not within their docs. I’ll shoot them an email and let them know.