Re: update without SET ?

Hector,
I think I was not clear. My function will be called from JDBC. The input
parameter will be a JSON document which will contain data for a single row
in a table. If the row does not exist I will INSERT it. ( I've got no
problem with the insert.) If the record already exists, I will update it.
There are 240 columns in the table. The selection of columns which must be
updated is random. So, there are 3 update possibilities for each table
column.

1) the column/key is named in the JSON document and the VALUE is not null.
Action: Simply update the table column with the new value.
2) the column/key is named in the JSON document and the VALUE is NULL.
Action: update the table column with a null value.
3) the column/key is not named in JSON document. Action: retain the
existing value in the db record (do not nullify it)

So, it is critical that I distinguish between an EXISTING key with a value
of NULL and a non-Existing key.
unfortunately this:
select string_agg(key||'='||value,',') as mycol from
json_each_text('{"key":22,"header":44,"ident":66,"static01":null,"static02":"that"}')
;

and this:
select string_agg(key||'='||value,',') as mycol from
json_each_text('{"key":22,"header":44,"ident":66,"static02":"that"}') ;

The result of both of the above queries:
"key=22,header=44,ident=66,static02=that"

I have no way to know if I should set static01 to null or leave it as is.

Thanks!
Mike

On Wed, Feb 3, 2016 at 12:59 PM, Hector Vass <hector(dot)vass(at)metametrics(dot)co(dot)uk>
wrote:

> json is really just key-value pairs..
>
> why not take the same approach and store the data in a temp table as
> key-value then have a static sumif
>
>
> with jsonrows as(
> select 1::int as r,key,value
> from json_each_text('{"key":22,"header":44,"ident":66,"static01":"this","static02":"that"}')
> union
> select 2::int as r,key,value
> from json_each_text('{"key":22,"header":44,"ident":66,"static03":"thatsit"}')
> )
> select
> r,
> max(case when key='key' then value::int else null end) as key,
> max(case when key='header' then value::int else null end) as header,
> max(case when key='ident' then value::int else null end) as ident,
> max(case when key='static01' then value::varchar(200) else null end) as
> static01,
> max(case when key='static02' then value::varchar(200) else null end) as
> static02,
> max(case when key='static03' then value::varchar(200) else null end) as
> static03
> from jsonrows
> group by r
> ;
>
> alternative is I guess a simple function
>
>
>
> Hector Vass
> 07773 352 559
> 01666 820 008
>
> MetaMetrics, International House, 107 Gloucester Road, Malmesbury,
> Wiltshire, SN16 0AJ
>
> [image: MetaMetrics – Marketing Analytics Consultancy]
> <http://www.metametrics.co.uk/>
>
> This e-mail and any attachments are confidential and for the attention of
> the addressee only. If you are not the intended recipient, any use,
> disclosure or copying of this document is unauthorised. If you have
> received this document in error please immediately notify the sender and
> delete this e-mail from your system. Whilst all emails sent by MetaMetrics
> are scanned using up-to-date virus scanning software, MetaMetrics Ltd.
> accepts no liability for any loss or damage which may be caused by software
> viruses and recommend that you conduct your own virus checks on all
> attached materials. Please note that any attached materials remain the
> exclusive property of MetaMetrics Ltd. unless expressly stated otherwise.
> Metametrics Limited is a limited company registered in England & Wales.
> Registered number 05453613. Registered offices at 86 Shirehampton Road,
> Stoke Bishop, Bristol, BS9 2DR
>
>
>
> ------------------------------
> *From:* pgsql-sql-owner(at)postgresql(dot)org <pgsql-sql-owner(at)postgresql(dot)org>
> on behalf of Michael Moore <michaeljmoore(at)gmail(dot)com>
> *Sent:* 03 February 2016 18:49
> *To:* postgres list
> *Subject:* [SQL] update without SET ?
>
> I want to UPDATE a table but I will not know until run time which columns
> will be updated. I would like to do something like this:
> update mytest t (SELECT * FROM
> json_populate_record(null::mytest,'{"key":22,"header":44,"ident":66,"static01":"this","static02":"that"}'));
>
> In other words, I will be receiving a json document on an input parameter.
> The columns named in the json document can be a sub set of those that exist
> in the table.
>
> tia Mike
>