Editing Field standardisation (section)

===Context===
Field names need to be unique only within a ''service''. Therefore, there is no benefit in making detailed 'helpful' fieldnames such as <code>WhanauOraTotalSpend</code> and <code>TupaiaTotalSpend</code>. A better strategy is to simply name each <code>TotalSpend</code>, and then it becomes much easier to aggregate spend across services<ref>Or indeed, joining any data point across services.</ref> if there is a good use case for doing so.

In the first example, a sample query would need to look something like:<ref>Note this has been drafted as an example without testing.</ref>

<syntaxhighlight lang="sql" line>
with ctea as
(
	select
	a.clientid,
	sum(j."FamilyTotalSpend")
	
	from kotahi.activities a,
		jsonb_to_record(customfielddatajson) 
			as j(
				"FamilyTotalSpend" float
				)
	
	where 
		activitytypeid  = 27 --Whanau ora request submission
		and
		customfielddatajson->>'FinanceCompleted' = 'on' --has been paid
	group by a.clientid
)
,
cteb as 
(
	select
	a.clientid,
	sum(j."TotalSpend")
	
	from kotahi.activities a,
		jsonb_to_record(customfielddatajson) 
			as j(
				"TotalSpend" float
				)
	
	where 
		activitytypeid  = 32 --Transition to adulthood financial assistance
		and
		customfielddatajson->>'FinanceCompleted' = 'on' --has been paid
	group by a.clientid
)

select 
a.clientid,
sum(a.FamilyTotalSpend,b.TotalSpend) "TotalSpend"

from ctea a
left join cteb b
on a.clientid = b.clientid

group by a.clientid;
</syntaxhighlight>

In the second example, the approach is much simpler:

<syntaxhighlight lang="sql" line>
select
a.clientid,
sum(j."TotalSpend")

from kotahi.activities a,
	jsonb_to_record(customfielddatajson) 
		as j(
			"TotalSpend" float
			)

where 
	activitytypeid in (27,32)
	and
	customfielddatajson->>'FinanceCompleted' = 'on' --has been paid
group by a.clientid;
</syntaxhighlight>

Also note that <code>jsonb_to_record</code> seems easier to cast data types from a JSON array for aggregation, but there might be better ways.<ref>Postgres 17 has [https://www.postgresql.org/docs/17/functions-json.html#FUNCTIONS-SQLJSON-TABLE JSON-TABLE], for example.</ref>