sql/postgresql-entrypoint-initdb.d/09_json.sql

create table item (
  id bigint primary key,
  data jsonb
);

create index idx_item_data
  on item using gin (data jsonb_path_ops);

alter table item
add column description_tsv tsvector generated always as (
  to_tsvector('french', data->>'description')
) stored;

create index idx_item_description_tsv
  on item
  using gin (description_tsv);

alter table item
add column search_tsv tsvector generated always as (
  setweight(to_tsvector('french', coalesce(data->>'description', '')), 'A') ||
  setweight(to_tsvector('french', coalesce(data->>'nom', '')), 'B') ||
  setweight(to_tsvector('french', coalesce(data->>'categorie', '')), 'C')
) STORED;


CREATE INDEX idx_item_search_tsv
  ON item
  USING gin (search_tsv);

/*
SELECT
  id,
  ts_rank(search_tsv, plainto_tsquery('french', 'miel lavande')) AS rank
FROM item
WHERE search_tsv @@ plainto_tsquery('french', 'miel lavande')
ORDER BY rank DESC;
*/

truncate table item;

DO $$
DECLARE
  f TEXT;
BEGIN
  FOR f IN SELECT pg_catalog.pg_ls_dir('/tmp/json')
  LOOP
    IF right(f, 5) = '.json' THEN
      BEGIN
        RAISE NOTICE 'Import du fichier : %', f;

        INSERT INTO item (id, data)
        VALUES (
            replace(f, '.json','')::bigint,
            pg_read_file('/tmp/json/' || f)::jsonb
        )
        ON CONFLICT (id) DO UPDATE
        SET data = EXCLUDED.data;

      EXCEPTION WHEN OTHERS THEN
        RAISE WARNING 'Erreur lors de l''import du fichier % : %', f, SQLERRM;
      END;
    END IF;
  END LOOP;
END;
$$ LANGUAGE plpgsql;