PostgreSQL JSON Data Type?

Hi all,

I have a PostgreSQL table delivered by a vendor with a field in the JSON data field type.

When I query it in cfml, I get the following in Lucee:

and this in ACF:

postgre_lucee_dev_2

but when running the query in DBeaver, it returns the following text string:

"{\"neutral_comments\":\"\\\"\\\"\",\"answers\":\"[{\\\"id\\\":3795,\\\"text\\\":\\\"Arthur Frank\\\",\\\"html\\\":\\\"\\\",\\\"comments\\\":\\\"\\\",\\\"comments_html\\\":\\\"\\\",\\\"weight\\\":100.0},{\\\"id\\\":870,\\\"text\\\":\\\"Anne Frank\\\",\\\"html\\\":\\\"\\\",\\\"comments\\\":\\\"\\\",\\\"comments_html\\\":\\\"\\\",\\\"weight\\\":0.0},{\\\"id\\\":211,\\\"text\\\":\\\"Lucy Grealy\\\",\\\"html\\\":\\\"\\\",\\\"comments\\\":\\\"\\\",\\\"comments_html\\\":\\\"\\\",\\\"weight\\\":0.0},{\\\"id\\\":7607,\\\"text\\\":\\\"Arthur Kleinman\\\",\\\"html\\\":\\\"\\\",\\\"comments\\\":\\\"\\\",\\\"comments_html\\\":\\\"\\\",\\\"weight\\\":0.0},{\\\"id\\\":8013,\\\"text\\\":\\\"Anne Hawkins\\\",\\\"html\\\":\\\"\\\",\\\"comments\\\":\\\"\\\",\\\"comments_html\\\":\\\"\\\",\\\"weight\\\":0.0}]\",\"name\":\"\\\"Question\\\"\",\"question_type\":\"\\\"multiple_choice_question\\\"\",\"question_text\":\"\\\"<p><strong>Who developed the three different categories of \\\\\\\"Narratives of Illness\\\\\\\"?</strong></p>\\\"\",\"correct_comments\":\"\\\"\\\"\",\"incorrect_comments\":\"\\\"\\\"\",\"text_after_answers\":\"\\\"\\\"\",\"points_possible\":1.0}"

What’s the proper way to query and parse this data in cfml?

Thanks!

Just a quick follow up, when running:

deserializeJSON(QuestionJSON.question_data)

it does return the same string as DBeaver, and then if I deserializeJSON() the other fields that contain JSON, it parses.

So kind of “de-nesting” the original JSON from the field works.

If anyone knows any good shortcuts outside of running recursive deserializeJSON() calls, I’m all ears.

Thanks!

You have to use toString() method on each json field.

<cfquery name="q" datasource="wineshipping"> 
    SELECT json_field
    FROM testtable
    LIMIT 1
</cfquery>

<cfdump var="#q.json_field.toString()#" label="json_field">

image

1 Like