0

I'm learning Postgres and have a test project with generating json data that served with NodeJS. So I decided to add Postgres;

I've created 4 tables and one helper table, because data shape is like so

// atricles.json
[
 {
  "id": 1,
  "categories": [1,2,3]
 }
]
// categories.json
[
 {
  "id": 1,
  "label": 'someName'
 }
]

Then I find interesting method to fill my Database. It parse json files in SQL script and insert values to the target table. I've successfully inserted data to all my tables.

BEGIN;
CREATE temporary TABLE categories_json (values TEXT) ON COMMIT DROP;
\copy categories_json FROM './mocks/categories.json';

INSERT INTO t_category(id, label)
SELECT CAST(values->>'id' AS bigint) AS id,
       CAST(values->>'label' AS character varying) AS label
FROM (select json_array_elements(values::json) AS values FROM categories_json) a;
COMMIT;

except one - atricle_categories (which is "many to many" relationship).

COLUMNS

article_id
category_id
pkey pk_article_id_category_id (article_id, category_id)

fkey fk_article_id references t_article(id)
fkey fk_category_id references t_category(id)

so, the problem is...

I dont know how to fill this table. I need to fill 2 columns from this table. And I want to do it with the similar way as I fill other tables.

Here I have a sketch script. I found a method for looping with the max and counter variables when you select max from id column of the cached table. But with other commands there are many errors (sintax, order...). It is very difficult for my. Can you help me, please?

Sketch script which I execute through PSQL(terminal) #psql -h localhost -U postgres -f sql/xxx.sql

THIS SHOULD BE FIXED

\connect typoteka

--------------------------------------------------------------------------------
DECLARE
  counter INTEGER := 1,
  maximum INTEGER := 0;
------------------------------ fill articles table -----------------------------
BEGIN;

CREATE temporary TABLE articles_json (values TEXT) ON COMMIT DROP;
\copy articles_json FROM './mocks/articles.json';

CREATE temporary TABLE xxx(id, categories) ON COMMIT DROP AS
SELECT
       CAST(values->>'id' AS INTEGER) AS id,
       CAST(values->>'categories' AS TEXT) AS categories
FROM (SELECT json_array_elements(values::json) AS values FROM articles_json) a;

SELECT maximum = COUNT(id) FROM xxx;

WHILE counter <= maximum
LOOP
    SELECT (id, categories) FROM xxx
    WHERE id = counter

    counter := counter + 1
COMMIT;
--------------------------------------------------------------------------------

The result table should look like this:

  article_id | category_id  
-------------+------------
           1 | 1
           1 | 2
           1 | 5
           2 | 1
           2 | 3
            ...
Sergey Topolov
  • 21
  • 9
  • 1
    It's unclear to me if you have one JSON document with articles and another one with categories, or if both are combined in a single one. If it's a single JSON document, then please show us the exact one you have, your example JSON isn't a valid single JSON document. –  Jul 20 '20 at 06:05
  • the example was from two json files. I just wanted to show the data shapes. all nesessury info to fill the result table is in one file - articles.json – Sergey Topolov Jul 20 '20 at 13:20
  • 1
    Can you then please **[edit]** your question and show the structure of the `articles.json`? –  Jul 20 '20 at 13:21
  • I just need to loop through it and fill the result table with atricleId and categoryId. – Sergey Topolov Jul 20 '20 at 13:21
  • the combined pkey column I think will be automatically filled. if not, It should be also filled by joined values – Sergey Topolov Jul 20 '20 at 13:23
  • I've already edit it. it is an array of articles with two necessary fields to solve this. I think there is enough information to understand the problem. You don't need file categories.json at all. – Sergey Topolov Jul 20 '20 at 14:38

0 Answers0