It is possible to create a Function that takes as parameter a JSON and goes through this JSON saving the data in a table, but filtering

Question

It is possible to create a Function that takes as parameter a JSON and goes through this JSON saving the data in a table, but filtering the Json. Today is inserting direct json without filter.

Example:

I’m getting this json as input in proc.:

    {
    "Documento": {
        "Apolice": [
            {
                "cdEmpresa": "01",
                "cdSucursal": "010",
                "cdCarteira": "431",
                "dsTipoDocumento": "A",
                "idApolice": "501741",
                "idEndosso": "000000",
                "ItemProp": [
                    {
                        "idLocal": "000001",
                        "dsEndereco": "RUA RUA RUA",
                        "dsComplemento": "CASA",
                        "dsCidade": "BELO HORIZONTE",
                        "dsUF": "MG"
                    }
                ],              
                "ItemAuto": [
                    {
                        "nrItem": "000001",
                        "cdModelo": "0017664",
                        "nmMarca": "VOLKSWAGEN",
                        "nmModelo": "TRENDLINE 1.0 FLEX 12V 5P",
                        "nmTipo": "FOX",
                        "aaFabricacao": "2016",
                        "aaModelo": "2017"
                    },
                    {
                        "nrItem": "000001",
                        "cdModelo": "0017664",
                        "nmMarca": "VOLKSWAGEN",
                        "nmModelo": "TRENDLINE 1.0 FLEX 12V 5P",
                        "nmTipo": "FOX",
                        "aaFabricacao": "2016",
                        "aaModelo": "2017"
                    },
                    {
                        "nrItem": "000001",
                        "cdModelo": "0017664",
                        "nmMarca": "VOLKSWAGEN",
                        "nmModelo": "TRENDLINE 1.0 FLEX 12V 5P",
                        "nmTipo": "FOX",
                        "aaFabricacao": "2016",
                        "aaModelo": "2017"
                    },
                    {
                        "nrItem": "000001",
                        "cdModelo": "0017664",
                        "nmMarca": "VOLKSWAGEN",
                        "nmModelo": "TRENDLINE 1.0 FLEX 12V 5P",
                        "nmTipo": "FOX",
                        "aaFabricacao": "2016",
                        "aaModelo": "2017"
                    }
                ]
            }
        ],
        "Cliente": [
            {
                "cdCliente": "1",
                "dsTipoDocto": "P",
                "nrCpfCnpj": "919191919292",
                "nmCliente": "JOSE JOSE JOSE"
            }
        ],
        "EnderecoCobranca": [
            {
                "dsEndereco": "RUA RUA RUA",
                "nrEndereco": "280",
                "dsComplemento": "",
                "dsBairro": "INTEGRAÇÃO",
                "dsCidade": "PAROBE",
                "dsUF": "RS"
            }
        ],
        "Mensagem": []
    }
}

And I’d like to filter the Array of Apolice, in the case of the fields: cdEmpresa, cdSucursal, etc..., and insert within their respective fields.

My Proc is :

    CREATE OR REPLACE FUNCTION schema.tabela(entrada json)
  RETURNS void as

        'INSERT INTO schema."tabela" 
        SELECT * FROM json_populate_recordset 
        (null::schema."tabela", $1);'

  LANGUAGE sql;

Answer 1

I was able to perform a procedure where I read the input Json file; I insert in a time table; Then I enter again in another table inserting the fields I have in result of the query using json Postgre functions. Example:

-- resultThis where saves the result of the select performed in json:

  resultTeste := (select dados ->'Documento'->'Apolice' from _teste as result);

--Inserts the resultTeste record into the corresponding Json fields of table temp:

INSERT INTO _testeDados select * from json_populate_recordset(null::_testeDados,resultTeste);