What is the best alternative for the following situation?

Question

I am using the JSONB field in my Postgresql database to store the next document. I have thousands of documents. I need to create reports with this data, but the search is very slow.

If I need to create a report indicating the new users of the month, I need to go through the entire document, comparing if the user is in one month and not in another.

Message Document:

[{"recipient":1,"user":4,"created_at":"2016-11-10","content":"Duis aliquam convallis nunc.","is_sender_user":true},
{"recipient":1,"user":18,"created_at":"2016-12-10","content":"Proin eu mi.","is_sender_user":false},
{"recipient":1,"user":4,"created_at":"2016-11-20","content":"In hac habitasse platea dictumstm.","is_sender_user":true},
{"recipient":1,"user":20,"created_at":"2016-12-14","content":"Donec ut dolor.","is_sender_user":true},
{"recipient":1,"user":13,"created_at":"2016-12-06","content":"Nulla mollis molestie lorem. Quisque ut erat. Curabitur gravida nisi at nibh.","is_sender_user":true}]

It would be better to create a User table and create a JSONB message field to hold your messages. Or how can I create my report using JSONB queries?

Answer 1

, jsonb, created_at user

create INDEX td002_si3 ON testData002 (substring(doc->>'created',0,8),(doc->>'user'));

SELECT 
      substring(doc ->> 'created', 0, 8) AS m,
      ARRAY_AGG(DISTINCT doc ->> 'user')          AS users
    FROM testData002
    GROUP BY substring(doc ->> 'created', 0, 8)

GroupAggregate  (cost=0.28..381.52 rows=3485 width=50)
  Group Key: ""substring""((doc ->> 'created'::text), 0, 8)
  ->  Index Scan using td002_si3 on testdata002  (cost=0.28..294.28 rows=3500 width=50)

,

create table testData002 as 
     select row_number() OVER () as id
           ,jsonb_build_object('created',dt::DATE
                              ,'user',(random()*1000)::INT) as doc 
       from generate_series(1,10),generate_series('2016-01-01'::TIMESTAMP,'2016-12-15'::TIMESTAMP,'1 day'::INTERVAL) as dt;

Answer 2

: . , . , , .

JSONB, , : , , , ; , . JSONB , , , , , .