Here is the activity field shape (se select non empty fields):
select jsonb_pretty(activity::jsonb) from adverts where activity::jsonb ->> 'exactMatchesTitle' != '[]' and activity::jsonb ->> 'exactMatchesText' != '[]';
{ +
"exactMatchesText": [ +
{ +
"id": 1146, +
"value": "delivery driver", +
"matched": "delivery driver" +
}, +
{ +
"id": 1195, +
"value": "cargo vehicle driver", +
"matched": "delivery driver" +
}, +
{ +
"id": 1883, +
"value": "Cooks", +
"matched": "Cooks" +
}, +
{ +
"id": 1884, +
"value": "Cooks", +
"matched": "Cooks" +
}, +
{ +
"id": 1885, +
"value": "cook", +
"matched": "cook" +
}, +
{ +
"id": 1895, +
"value": "Waiters", +
"matched": "Waiters" +
}, +
{ +
"id": 1888, +
"value": "grill cook", +
"matched": "line cook" +
}, +
{ +
"id": 1897, +
"value": "waiter/waitress", +
"matched": "waitress" +
}, +
{ +
"id": 1897, +
"value": "waiter/waitress", +
"matched": "waiter" +
}, +
{ +
"id": 2022, +
"value": "management assistant", +
"matched": "assistant manager" +
}, +
{ +
"id": 2021, +
"value": "executive assistant", +
"matched": "assistant manager" +
}, +
{ +
"id": 3241, +
"value": "presenter", +
"matched": "hostess" +
} +
], +
"exactMatchesTitle": [ +
{ +
"id": 1146, +
"value": "delivery driver", +
"matched": "delivery driver" +
}, +
{ +
"id": 1195, +
"value": "cargo vehicle driver", +
"matched": "delivery driver" +
} +
] +
}
To make the histogram of the matched words:
select jsonb_array_elements((activity::jsonb ->> 'exactMatchesTitle')::jsonb) ->> 'matched' as matches, count(*) as count from adverts group by matches order by count DESC;
matches | count
-------------------------------------+-------
carpenter | 1344
truck driver | 1281
electrician | 1232
housekeeper | 633
handyman | 627