Comparison with recorded data such as JSON

Asked

Viewed 284 times

1

I have a date field that is saved to the field in JSON format, along with other values

{"data_limite":"14\/07\/2016","data_programada":null,"admin_agendamento":"4813"}

This field "deadline", I need, in another screen, put in a CASE-WHEN to check if that date is the 24, 48 or 72 hours to arrive today.

This is my question: How to perform a comparison in a CASE-WHEN with a data saved with other values?

EDIT:My postgres is 8.3, so without JSON support, it is saved in a TEXT field

EDIT 2: Excerpt from SQL that I have to execute:

...
CASE
    WHEN tbl_cliente_admin.nome IS NOT NULL AND tbl_hd_chamado_extra.hd_chamado NOT NULL AND finalizada IS NULL AND array_campos_adicionais NOT NULL AND (AQUI TENHO QUE COMPARAR)::date - CURRENT_DATE >= 3
    THEN 3
    WHEN tbl_cliente_admin.nome IS NOT NULL AND tbl_hd_chamado_extra.hd_chamado NOT NULL AND finalizada IS NULL AND array_campos_adicionais NOT NULL AND (AQUI TENHO QUE COMPARAR)::date - CURRENT_DATE > 1
    THEN 2
    WHEN tbl_cliente_admin.nome IS NOT NULL AND tbl_hd_chamado_extra.hd_chamado NOT NULL AND finalizada IS NULL AND array_campos_adicionais NOT NULL AND (AQUI TENHO QUE COMPARAR)::date - CURRENT_DATE <= 1
    THEN 1
    ELSE 4
END AS termino_atendimento
...
postgresql
  • 2

    Do you need to make this comparison directly in the database or is it in the application? (PHP, etc..)

  • Yes, right in the bank, because I have no previous parameters to search before and do in the application. I need to take this "data_limite" and make our beloved INTERVAL 1, 2 or 3 days.

  • I found this link might help: http://stackoverflow.com/questions/30836025/postgresql-query-between-time-range-using-jsonb-field according to the link ->> extracts the field from within the Json

  • It would help @Lucasqueirozribeiro, but as I said in my EDIT, my bank is 8.2, it doesn’t support JSON. This support is from 9. By my walks, I would need to do a sub-select to search inside each CASE, but it would be too heavy.

  • 1

    I found what I needed: split_part(). I did the tests and answered me. Thank you for your attention.

1 answer

1


Factoring in your case:

case
    tbl_cliente_admin.nome is not null and
    tbl_hd_chamado_extra.hd_chamado is not null and
    finalizada is null
    when true then
        case split_part(split_part(json, '","', 1), '":"', 2)::date - current_date
            when 0 then 1
            when 1 then 1
            when 2 then 2
            else 3
        end
    else 4
end as termino_atendimento

Note that both your case and this factored version will return 1 for dates prior to the current.

Browser other questions tagged postgresql

You are not signed in. Login or sign up in order to post.