JSON_TABLE since postgresql v17

JSON_TABLE is a function which displays json data as rows and columns. This comes in handy when you want to create a database view for existing json data. For my usecase I sync a lot of records from an external service (using json REST) to a local postgresql table with the following structure:

create table public.imported_records (
    id integer primary key,
    uuid UUID not null,
    updated_on varchar(30) not null,
    outdated boolean not null default false,
    record jsonb not null,
    last_check timestamp without time zone not null
);

Every field is populated with external information except for outdated and last_check. Now the record is a huge JSON document, but I want to query the records like a normal table. Since postgresql verison 17, there’s JSON_TABLE, wich we can use to create a view of the following data:

insert into
  public.imported_records (id, UUID, updated_on, record, last_check)
values
  (
    1337,
    '24bae3ff-5d6a-492c-bf30-ef17bf777967',
    '2026-02-07T10:50:13.0',
    '{
      "FullName": "Sandro",
      "Homepage": "https://sandro.zip",
      "Address": "...",
      "Mobile": "+1 2345",
      "Id": 1337,
      "UpdatedOn": "2026-02-07T10:50:13.0",
      "UniqueId": "24bae3ff-5d6a-492c-bf30-ef17bf777967"
    }',
    NOW()
  );

Usually you could now query the json like so select record->'Id' as record_id from imported_records;. But instead let’s create a view using JSON_TABLE:

create view recordsview as (
  select
    jt.*
  from
    imported_records, JSON_TABLE (record, '$'
      COLUMNS (
        full_name text PATH '$.FullName',
        homepage text PATH '$.Homepage',
        address text PATH '$.Address',
        mobile text PATH '$.Mobile',
        id integer PATH '$.Id',
        updated_on text PATH '$.UpdatedOn',
        uuid text PATH '$.UniqueId'
      )
    ) AS jt
);

Now we can query the view without touching any json:

sandro=# select * from recordsview ;
 full_name |      homepage      | address | mobile  |  id  |      updated_on       |                 uuid
-----------+--------------------+---------+---------+------+-----------------------+--------------------------------------
 Sandro    | https://sandro.zip | ...     | +1 2345 | 1337 | 2026-02-07T10:50:13.0 | 24bae3ff-5d6a-492c-bf30-ef17bf777967

Why?

To add a little bit of background: The schema definition above is by no means ideal or optimized, but emerged as a practical solution for synchronizing hundrets of thousands of rows from an external service. The problem with this external service is that it internally uses a NoSQL database and fails to ensure schema validation; This means some records miss fields or throw errors when accessed. I don’t want to deal with this shitty api, that’s why I sync to a local db for data analysis. Each record type gets its own table; The name is infered from the record type of the external service, e.g. Users and UserContracts. A generalizes scraper can then synchronize all records with just the URL of the record type.