PostgreSQL JSON Data Type?

dev
1

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:

postgre_lucee_dev_1
postgre_lucee_dev_1723Ă—700 35.1 KB

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!

2

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!

3

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