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 
              
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?
              
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.
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.