Display, search and update attributes from a JSON field

This example shows you how to display information from a JSON field using smart fields.

‌ Here a user record has a field contact of the type JSONB that includes an email and a phone attribute. On the users collection, we want to implement smart fields (called email and phone) that will display the value of information contained within the JSON as separate fields and be able to update these fields.

29 July, 2020 - Loom RecordingLoom

Requirements

‌

• An admin backend running on forest-express-sequelize

• A PostgreSQL database including a table with a JSONB column

‌

How it works

Directory: /models

This directory contains the users.js file where the model is declared.

/models/users.js

module.exports = (sequelize, DataTypes) => {
  const { Sequelize } = sequelize;
  const Users = sequelize.define('users', {
    contact: {
      type: DataTypes.JSONB,
    },
  }, {
    tableName: 'users',
    timestamps: false,
    schema: process.env.DATABASE_SCHEMA,
  });

  Users.associate = (models) => {
  };

  return Users;
};

Directory: /forest

This directory contains the users.js file where the smart fields email and phone are implemented.

The get() method retrieves the value that will be displayed in the UI and the set() method allows to specify the logic to be followed with regards to the input value entered upon edit. Here we will handle the update at the routes level so we just pass the object as a result of the set() method.

/forest/users.js

const { collection } = require('forest-express-sequelize');

collection('users', {
  actions: [],
  fields: [
    {
      field: 'email',
      type: 'String',
      get: (object) => {
        return object.contact ? object.contact.email : null;
      },
      search: (query, search) => {
        // Add search on contact->email field of the JSONB
        const searchCondition = {
          'contact.email': { [Op.iLike]: `%${search}%` },
        };
  
        query.where[Op.and][0][Op.or].push(searchCondition);
        return query;
      },    
      set: (object, email) => {
        return object;
      },
    }, {
      field: 'phone',
      type: 'String',
      get: (object) => {
        return object.contact ? object.contact.phone : null;
      },
      set: (object, phone) => {
        return object;
      },
    },
  ],
  segments: [],
});

Directory: /routes

This directory contains the users.js file where the implementation of the PUT route for the update action is handled.

Note that a JSON field cannot be updated attribute per attribute, it needs to be updated as a whole as not to erase the values already contained in it.

/routes/users.js

const express = require('express');
const { PermissionMiddlewareCreator, RecordGetter } = require('forest-express-sequelize');
const { users } = require('../models');

const router = express.Router();
const permissionMiddlewareCreator = new PermissionMiddlewareCreator('users');
//...


// Method to update a JSON object
function updateJson(record, formAttributes, jsonField, jsonAttributes) {
  const json = record[jsonField] || {};
  jsonAttributes.forEach((attribute) => {
    json[attribute] = attribute in formAttributes ? formAttributes[attribute] : json[attribute];
  });
  return json;
}

// Update a User
router.put('/users/:recordId', permissionMiddlewareCreator.update(), (request, response, next) => {
  const recordGetter = new RecordGetter(users, request.user, request.query);
  const attr = request.body.data.attributes;
  recordGetter.get(request.params.recordId).then((record => {
    attr.contact = updateJson(record, attr, 'contact', ['email', 'phone']);
    return record.update(attr);
  }))
  .then((recordUpdated) => recordGetter.serialize(recordUpdated))
  .then((recordSerialized) => response.send(recordSerialized))
  .catch(next);
});

//...

module.exports = router;