Storing objects in JSONB column using Postgres/Knex

Question

I created a graphs table on my back end to store graph related data. Here is the schema from the Knex migration file:

exports.up = function(knex) {
  return (
      knex.schema
        .createTable('users', tbl => {
            tbl.increments();
            tbl.string('username', 255).notNullable();
            tbl.string('password', 255).notNullable();
            tbl.string('name', 255).notNullable();
            tbl.string('email', 255).unique().notNullable();
        })
        .createTable('graphs', tbl => {
          tbl.increments();
          tbl.string('graph_name', 255).notNullable();
          tbl.jsonb('graph_info').notNullable();
          tbl
            .integer('user_id')
            .unsigned()
            .notNullable()
            .references('id')
            .inTable('users')
            .onDelete('CASCADE')
            .onUpdate('CASCADE');
        })
  )
};

Here is an example of the type of data that I am trying to store in the jsonb column in my database:

{
  labels: ['Axis1', 'Axis2', 'Axis3'],
  datasets: [
        {
          label: 'Dataset1',
          borderDash: [0, 0],
          backgroundColor: '#fff',
          data: [25, 14, 22],
        },
      ],
  title: 'Graph1',
}

Now here is the request that I tried to send through Postman:

{
    "graph_name": "test10",
    "graph_info": "{
        labels: ['Axis1', 'Axis2', 'Axis3'],
        datasets: [
            {
              label: 'Dataset1',
              borderDash: [0, 0],
              backgroundColor: '#fff',
              data: [25, 14, 22],
            },
                ],
            title: 'Graph1'
        }",
    "user_id": "1"
}

I received the following error: SyntaxError: Unexpected token in JSON at position 44. I came across this article when I was trying to figure out what is going on:

sending nested json object using postman

I have my Content-Type header set to application/json, and Postman isn't giving me a signal that I'm sending a bad request. I'm not sure what the problem is.

It's also possible that I'm thinking about this the wrong way. When I was looking at the Postgres docs, it seemed to me that the best way to store the type of object that I'm trying to store would be with a jsonb column. But if that is not the case, I'm open to suggestions. Any help would be greatly appreciated.

score 5 · Accepted Answer · edited Jan 02 '20 at 11:57

5

From the look of graph_info in the result, it is not a valid JSON, but a string that contains js object.

When you saves your data to the JSONB column, you need to apply the JSON.stringify method on the object.

For example like this:

{
    "graph_name": "test10",
    "graph_info": JSON.stringify({
        labels: ['Axis1', 'Axis2', 'Axis3'],
        datasets: [
            {
              label: 'Dataset1',
              borderDash: [0, 0],
              backgroundColor: '#fff',
              data: [25, 14, 22],
            },
        ],
        title: 'Graph1'
    }),
    "user_id": "1"
}

edited Jan 02 '20 at 11:57

Mikael Lepistö

18,909
3
68
70

answered Jan 02 '20 at 08:21

felixmosh

32,615
9
69
88

Thank you for getting back to me. I'm working with other devs who are building the front end. Is there a way that I can test this using Postman? I know I can have the front end devs stringify the graph data before they send it. But I also want to troubleshoot on my end. – cpppatrick Jan 02 '20 at 14:50
Just check what u het on the server, usually servers are parse json body by default, therefore you need to convert it back to a string – felixmosh Jan 02 '20 at 16:27

Storing objects in JSONB column using Postgres/Knex

1 Answers1