Does this support the Postgres JSON type?

[loopbackio]

Does this support the Postgres JSON type?

[ezraroi]

What about Array types? How we should define them?

[ssh24]

You can specify a column type to be explicitly postgres JSON type of the specific property in the model-definition.

Here is an example code snippet:

model-definition.json

{
  "name": "Business",
  "base": "PersistedModel",
  "idInjection": false,
  "options": {
    "validateUpsert": true
  },
  "properties": {
    "id": {
      "type": "number",
      "required": true,
      "id": true
    },
    "name": {
      "type": "string"
    },
    "location": {
      "type": "object",
      "postgresql": {
        "dataType": "json"
      }
    }
  },
  "validations": [],
  "relations": {},
  "acls": [],
  "methods": {}
}

boot-script.js

'use strict';
var util = require('util');
var _ = require('lodash');

module.exports = function(app) {
    var db = app.datasources.postgresqlDs;
    var Business = app.models.Business;

    db.automigrate(function(err) {
        if (err) throw err;
        console.log('\nAutomigrate completed');

        Business.create([{
            id: 1,
            name: 'Foo Inc.',
            location: {
                'United States': {
                    stores: 10
                },
                'United Kingdom': {
                    stores: 5
                },
                'Brazil': {
                    stores: 2
                }
            }
        }, {
            id: 2,
            name: 'Bar Inc.',
            location: {
                'United States': {
                    stores: 12
                },
                'United Kingdom': {
                    stores: 20
                },
                'Argentina': {
                    stores: 4
                }
            }
        }], function(err, result) {
            if (err) throw err;
            console.log('\nCreated instance: ' + util.inspect(result, {depth: 4}));

            Business.find(function(err, result) {
                if (err) throw err;
                console.log('\nFound instance: ' + util.inspect(result, {depth:4}))
            });
        });
    });
};

output

sakibs-mac:postgresql-31 ssh$ node .
Web server listening at: http://0.0.0.0:3000
Browse your REST API at http://0.0.0.0:3000/explorer

Automigrate completed

Created instance: [ { id: 1,
    name: 'Foo Inc.',
    location:
     { 'United States': { stores: 10 },
       'United Kingdom': { stores: 5 },
       Brazil: { stores: 2 } } },
  { id: 2,
    name: 'Bar Inc.',
    location:
     { 'United States': { stores: 12 },
       'United Kingdom': { stores: 20 },
       Argentina: { stores: 4 } } } ]

Found instance: [ { id: 1,
    name: 'Foo Inc.',
    location:
     { 'United States': { stores: 10 },
       'United Kingdom': { stores: 5 },
       Brazil: { stores: 2 } } },
  { id: 2,
    name: 'Bar Inc.',
    location:
     { 'United States': { stores: 12 },
       'United Kingdom': { stores: 20 },
       Argentina: { stores: 4 } } } ]

database table output & schema

testdb=# select * from Business;
 id |   name   |                                        location
----+----------+-----------------------------------------------------------------------------------------
  1 | Foo Inc. | {"United States":{"stores":10},"United Kingdom":{"stores":5},"Brazil":{"stores":2}}
  2 | Bar Inc. | {"United States":{"stores":12},"United Kingdom":{"stores":20},"Argentina":{"stores":4}}
(2 rows)

testdb=# \d Business;
    Table "public.business"
  Column  |  Type   | Modifiers
----------+---------+-----------
 id       | integer | not null
 name     | text    |
 location | json    |
Indexes:
    "business_pkey" PRIMARY KEY, btree (id)

Hopefully, this answers the question in this issue. Closing this as resolved. Feel free to reopen if needed.

[raymondfeng]

We do support 'JSON' or 'Object' as the data type. The value will be stored as a CLOB with stringified json.

[0xgeert]

Does this allow for filtering (the WHERE equivalent) on key-values in the the JSON?

[raymondfeng]

No. Embedded object for relational DBs doesn't support queries.

[dh376]

+1

[geekguy]

Any update on this ?

[geekguy]

@ssh24 - Can we use a find filter for some property in json column?

Something like,

Business.find({ where: { location -> brazil -> stores:  2 } });

If not, any plans to support?

[ssh24]

There is currently no support using embedded filters on JSON fields at the moment. I have added it as a feature request in our backlog. Patch is welcome. #266