Practical commands with JSON:
{
"language": "de",
"data": {
"persons": [
{
"firstname": "Julian",
"guestcards": [
{ "card_number": "Sexten101" },
{ "card_number": "Sexten102" }
]
},
{
"firstname": "Martina",
"guestcards": [
{ "card_number": "Sexten103" },
{ "card_number": "Sexten104" }
]
}
]
}
}
Select all firstame
SELECT (json_array_elements(_jsonin->'data'->'persons')->>'firstname')::text;
Select nested data inside JSON object
The following command returns one line per card number, and at the same time joins the first name from the parent element.
SELECT obj->>'firstname',
json_array_elements(obj->'guestcards')->>'card_number'
From json_array_elements(_jsonin->'data'->'persons') obj;
- - - - - - - - - - - -
{
"guestCards": [
{
"issuerCardTypeCodes": [
"AAAX",
"BBBB"
]
}
]
}
Select all array items
SELECT
json_array_elements((obj->>'issuerCardTypeCodes')::json)
From json_array_elements(_jsonin->'guestCards') obj;
{
"language": "en",
"guestcard_types": [
"1",
"2",
"3"
]
}
select * from json_array_elements_text((jsonin->>'guestcard_types')::json);
{
"language": "en",
"guestcard_types": [
1,
2,
3
]
}
Select (r.data)::NUMERIC from (select json_array_elements_text((jsonin->>'guestcard_types')::json)) as r(data);
__customerSupplierRids bigint[];
__customerSupplierRids = ARRAY(Select (r.data)::BIGINT from (select json_array_elements_text((jsonin->'filter'->>'customerSupplierRids')::json)) as r(data));
Build an object
json_build_object(
'companyKey', __compkey,
'appUserRid', __app_user_rid,
'fraktionKey', __frakey,
'language', __lngkey,
'filter', json_build_object(
'numbers', json_agg(
__ob_number::text
),
'languages', '[]'::json
),
'return', _jsonin->'return'
);
Count JSON items
Select json_array_length(_jsonin->'announcements') into __counter;
Control whether a key is output or not to influence the structure of the JSON.
You can di this, by using concatenation, jsonb_strip_nulls and a matching variable.
__jsonOut = json_build_object(
'success', true,
'accommodations', (
SELECT
json_agg(
jsonb_build_object(
'propName', my_prop_name,
) ||
jsonb_strip_nulls(
jsonb_build_object(
'contact', (
SELECT
(
jsonb_build_object(
'phone', phone,
'email', email,
'pecMail', pec_mail
)
)
WHERE __contact = true
)
)
) ||
jsonb_build_object(
... ... ...
Change a single value in JSON
JSONB_SET(__jsonOut::jsonb,'{modeResult}','"OK"', true)::text;
In this case __jsonOut::jsonb is an JSON object converted in JSONB
{modeResult} is the atribute to change
"OK" ist the new value
and true indicates that the JSON element will be created if not exists.
Combine json_array_elements with CASE WHEN
Select setting->>'exportForWage' as export_for_wage,
CASE
WHEN setting->>'timestampActive' IS null OR setting->>'timestampActive' = '' THEN setting->>'exportForWage'
ELSE setting->>'timestampActive'
END as timestampe_active
FROM (
Select json_array_elements(_jsonin->'inOut'->'settings')
AS setting
) a
The following NOT worked for me
SELECT
json_array_elements(data -> 'items') AS item,
CASE
WHEN (item ->> 'type') = 'book' THEN item ->> 'title'
WHEN (item ->> 'type') = 'movie' THEN item ->> 'name'
END AS title_or_name
FROM my_table