Closed bugthesystem closed 1 year ago
Schema (PostgreSQL v14)
CREATE TABLE test (
id INT,
payload jsonb null default '{}'::jsonb
);
INSERT INTO test (id) VALUES (1);
INSERT INTO test (id) VALUES (2);
ALTER TABLE test ADD needs JSONB NULL DEFAULT '[]'::jsonb;
INSERT INTO test (id, payload, needs)
VALUES (3, '{}', '[]');
INSERT INTO test (id, payload, needs)
VALUES (4, '{}', '[{"status":true, "label": "mama"}, {"status":false, "label": "mont"}]');
Query #1
Use
jsonb_array_elements()
to unnest the jsonb array and the aggregate functionjsonb_agg()
to get the result in a single row:
SELECT id, jsonb_agg(elem->'label') as active_needs
FROM test
cross join jsonb_array_elements(needs) as elem
where (elem->>'status')::boolean = true
group by id;
id | active_needs |
---|---|
4 | mama |
Description
Functionality to return
needs
of locations.discord username: @bug the system#9283
closes #issue
128
Please describe your changes, the reason for the changes, and the content of the code. Don't forget to list the dependencies that are affected by the change.
Implemented a flow that sends message content to Intent Extractor API to get detailed intent tags (needs) and serve from the backend API.
Prerequisites:
feeds_location
table firstNEEDS_RESOLVER_API_URL
should be setNEEDS_RESOLVER_API_KEY
env var should be set as wellTODOs
needs
is a string at the moment and needs to be updated to be stored as JSONB.GetLocations
andGetFeed
methods need to be updated to query theneeds
JSONB field instead of thestring
Things to do before you open the PR
Rules for opening PRs
Changes
How were these changes tested?
Please explain the actions you've taken to test these changes. Please also explain your testing environment.
Test Configuration: