Today I learned@Cybertec

Posts tagged "json"

Convert JSON to table type

The function json_populate_record can be used to cast a JSON object to a table type:

CREATE TABLE foo(bar int);

SELECT  *
FROM    json_populate_record(NULL::foo, '{"bar": 42}')
+-------+
| bar   |
|-------|
| 42    |
+-------+

This can also be used to insert into the table:

INSERT INTO foo
SELECT *
FROM   json_populate_record(NULL::foo, '{"bar": 42}')

Typescript json validation with io-ts

There's a Typescript library called io-ts that can help to strong-type json data fetched from the server and at the same time provide static typescript typing.

Problem

Imagine we have this line that fetches some data from an endpoint:

const employee = await fetchEmployee();

employee will probably have the any type. If we knew the shape of the employee object, we could create a type and cast employee:

type Employee {
  firstName: string;
  lastName: string;
}

const employee = await fetchEmployee() as Employee;

But now we are assuming the shape of employee, and if it changes in future versions of the backend, it can lead to annoying runtime errors such as accessing properties on undefined objects, which can be hard to track. We could validate it with a lib such as ajv, but we wouldn't be able to have a single source of truth.

Solution

With io-ts we can define a type like this:

import * as t from 'io-ts';

// The runtime type we will use to validate the data fetched from the server
const Employee = t.type({
  firstName: t.string,
  lastName: t.string,
});

// The static type. The above runtime type acts as the single source of truth
type Employee = t.TypeOf<typeof Employee>;

// Now we can do this (in pseudo-code)
const employee = Employee.decode(await fetchEmployee());

console.log(employee.firstName);  // works
console.log(employee.foo);        // typescript compile error

Now employee is correctly typed, and the shape of the data is validated at runtime.

Implementing the pseudo-code

The lib is great, but the documentation found in the repository's README is a little confusing. The easiest way I found to just validate data and throw if the shape is incorrect is like this:

import { getOrElse } from "fp-ts/lib/Either";  // io-ts has fp-ts as a peer dependency
import { failure } from "io-ts/lib/PathReporter";

const toError = (errors: any) => new Error(failure(errors).join('\n'));
const employee = getOrElse(toError)(Employee.decode(await fetchEmployee()));

if (employee instanceof Error) {
  throw employee;
}

console.log('the first name is', employee.firstName)

This steps can be easily extracted to a helper function.

Pretty Printing JSON

JSON is everywhere, but reading nested JSON without proper formatting can be a nightmare.

PostgreSQL

The function jsonb_pretty allows you to pretty print jsonb data.

\pset format unaligned
SELECT jsonb_pretty('{"name": "Lorenz", "team": {"name": "Team #1", "color", "blue"}}'::jsonb);

{
    "name": "Lorenz",
    "team": {
        "name": "Team #1",
        "color": "blue"
    }
}

Javascript

If you work with JSON data in Javascript, you surely know the function JSON.stringify. But did you know it can prettify your JSON as well?

JSON.stringify({"name": "Lorenz", "games_won": 4, "games_lost": 1}, null, 4)
                                      // number of spaces for indentation ^

{
    "name": "Lorenz",
    "games_won": 4,
    "games_lost": 1
}

Python

Python's json module adds functions to work with JSON.

>>> import json
>>> print(json.dumps({"players": [{"name": "Lorenz"}, {"name": "Philip"}]}, indent=4))

{
    "players": [
        {
            "name": "Lorenz"
        },
        {
            "name": "Philip"
        }
    ]
}

Command line using Python

You can also directly run the tool exposed by the json module from the command line:

$ echo '{"name": "Lorenz", "has_eyes": true}' | python3 -m json.tool

{
    "name": "Lorenz",
    "has_eyes": true
}