Comparison with data recorded as JSON

Question

Comparison with data recorded as JSON

Navigation

#1 by (1 votes)

1

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

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

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

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

EDIT : My postgres is 8.3, so without JSON support, it is written to a TEXT field

EDIT 2 : SQL Excerpt 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

asked by anonymous 11.07.2016 / 15:51

1 answer

Share object between two controllers in angularjs Array receiving two objects

score 1 · Accepted Answer

Facing 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 before the current one.