How to save and query a JSONB column that contains an array of hashes

18 Mar How to save and query a JSONB column that contains an array of hashes

Have you used JSONB from Postgres recently? I’m not going to talk about it but I think it’s pretty awesome and powerful, and I’m going to show some of my findings that I have made so far and actually was inspired by: https://www.reddit.com/r/PostgreSQL/comments/2u6ah3/how_to_use_json_to_recordset_on_json_stored_in_a/

Let’s get started by creating our table and inserting some data in it.

CREATE TABLE survey_responses
(
    id serial primary key,
    data jsonb
);

INSERT INTO survey_responses (data) VALUES 
  ('[{"col": "4", "row": "2", "answer": "answer1"}, {"col": "1", "row": "3", "answer": ""}]'),
  ('[{"col": "4", "row": "2", "answer": "answer1"}, {"col": "1", "row": "3", "answer": ""}]');

db/development_invesmark_surveys=# select * from survey_responses;
 id |                                          data
----+-----------------------------------------------------------------------------------------
  6 | [{"col": "4", "row": "2", "answer": "answer1"}, {"col": "1", "row": "3", "answer": ""}]
  7 | [{"col": "4", "row": "2", "answer": "answer1"}, {"col": "1", "row": "3", "answer": ""}]

Trying to use as recordset values

SELECT t.row, t.col, t.answer FROM survey_responses CROSS JOIN LATERAL json_to_recordset(survey_responses.data) as t(col text, row text, answer text)

if you receive this error:

ERROR:  function json_to_recordset(jsonb) does not exist
//or
ERROR:  function json_each(text) does not exist

HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

And you know that your Postgres version supports that function that means that you need to cast values in my case for example:
changing this

json_to_recordset(mytable.my_jsonb_field)
//to
json_to_recordset(mytable.my_jsonb_field::json)

that made the trick

So

SELECT t.row, t.col, t.answer FROM survey_responses CROSS JOIN LATERAL json_to_recordset(survey_responses.data) as t(col text, row text, answer text)

to:

SELECT t.row, t.col, t.answer FROM survey_responses CROSS JOIN LATERAL json_to_recordset(survey_responses.data::json) as t(col text, row text, answer text)

There you go now you should receive the following output:

 row | col | answer
-----+-----+---------
 2   | 4   | answer1
 3   | 1   |
 2   | 4   | answer1
 3   | 1   |

and also you are able to search things for example using where:

SELECT t.row, t.col, t.answer FROM survey_responses CROSS JOIN LATERAL json_to_recordset(survey_responses.data::json) as t(col text, row text, answer text) where t.col = '4'

will found:

 row | col | answer
-----+-----+---------
 2   | 4   | answer1
 2   | 4   | answer1

Another example

SELECT t.row, t.col, t.answer FROM survey_responses CROSS JOIN LATERAL json_to_recordset(survey_responses.data::json) as t(col text, row text, answer text) where t.col = '4' and survey_responses.id = 6

 row | col | answer
-----+-----+---------
 2   | 4   | answer1

Pretty awesome right? keep in touch and I’ll try to write more post about Postgres using jsonb columns.