2

I have a working function. But it only works if I literally supply both the city and country variables.

CREATE OR REPLACE FUNCTION gridref_by_address(city text, country text)
RETURNS SETOF research_db_gridreference AS $$
  SELECT id, data, version
  FROM research_db_gridreference, jsonb_array_elements(data -> 'addresses') addresses
  WHERE (
    addresses ->> 'city' = city
    AND
    addresses ->> 'country' = country
  )
$$ LANGUAGE sql STABLE;

This returns data such as:

| id            | data                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    | version    |
|---------------|---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|------------|
| grid.411921.e | {"id": "grid.411921.e", "name": "Cape Peninsula University of Technology", "links": ["http://www.cput.ac.za/"], "types": ["Education"], "labels": [{"label": "Kaapse Skiereiland Universiteit van Tegnologie", "iso639": "af"}], "status": "active", "weight": 1, "aliases": [], "acronyms": [], "addresses": [{"lat": -33.932222, "lng": 18.640278, "city": "Cape Town", "state": null, "line_1": "", "line_2": "", "line_3": null, "country": "South Africa", "primary": false, "postcode": "", "state_code": "", "country_code": "ZA", "geonames_city": {"id": 3369157, "city": "Cape Town", "license": {"license": "http://creativecommons.org/licenses/by/3.0/", "attribution": "Data from geonames.org under a CC-BY 3.0 license"}, "nuts_level1": null, "nuts_level2": null, "nuts_level3": null, "geonames_admin1": {"code": "ZA.11", "name": "Western Cape", "ascii_name": "Western Cape"}, "geonames_admin2": {"code": "ZA.11.CPT", "name": "City of Cape Town", "ascii_name": "City of Cape Town"}}}], "established": 2005, "external_ids": {"ROR": {"all": ["https://ror.org/056e9h402"], "preferred": "https://ror.org/056e9h402"}, "ISNI": {"all": ["0000 0001 0177 134X"], "preferred": null}, "OrgRef": {"all": ["3197244"], "preferred": null}, "FundRef": {"all": ["501100004512"], "preferred": null}, "Wikidata": {"all": ["Q1034468"], "preferred": null}}, "ip_addresses": [], "email_address": null, "relationships": [], "wikipedia_url": "http://en.wikipedia.org/wiki/Cape_Peninsula_University_of_Technology"}               | 2020-03-15 |
| grid.413335.3 | {"id": "grid.413335.3", "name": "Groote Schuur Hospital", "links": ["https://www.westerncape.gov.za/your_gov/163"], "types": ["Healthcare"], "labels": [], "status": "active", "weight": 1, "aliases": [], "acronyms": [], "addresses": [{"lat": -33.941, "lng": 18.463, "city": "Cape Town", "state": null, "line_1": "", "line_2": "", "line_3": null, "country": "South Africa", "primary": false, "postcode": "", "state_code": "", "country_code": "ZA", "geonames_city": {"id": 3369157, "city": "Cape Town", "license": {"license": "http://creativecommons.org/licenses/by/3.0/", "attribution": "Data from geonames.org under a CC-BY 3.0 license"}, "nuts_level1": null, "nuts_level2": null, "nuts_level3": null, "geonames_admin1": {"code": "ZA.11", "name": "Western Cape", "ascii_name": "Western Cape"}, "geonames_admin2": {"code": "ZA.11.CPT", "name": "City of Cape Town", "ascii_name": "City of Cape Town"}}}], "established": 1938, "external_ids": {"ROR": {"all": ["https://ror.org/00c879s84"], "preferred": "https://ror.org/00c879s84"}, "ISNI": {"all": ["0000 0004 0635 1506"], "preferred": null}, "OrgRef": {"all": ["840500"], "preferred": null}, "Wikidata": {"all": ["Q368400"], "preferred": null}}, "ip_addresses": [], "email_address": null, "relationships": [{"id": "grid.7836.a", "type": "Related", "label": "University of Cape Town"}, {"id": "grid.467135.2", "type": "Parent", "label": "Western Cape Department of Health"}], "wikipedia_url": "https://en.wikipedia.org/wiki/Groote_Schuur_Hospital"} | 2020-03-15 |

I want to be able to do something like:

CREATE OR REPLACE FUNCTION gridref_by_address(city text default null, state text default null, country text default null)
RETURNS SETOF research_db_gridreference AS $$
  SELECT id, data, version
  FROM research_db_gridreference, jsonb_array_elements(data -> 'addresses') addresses
  WHERE (
    addresses ->> 'city' = city -- omit if city = null
    AND
    addresses ->> 'state' = state -- omit if state = null
    AND
    addresses ->> 'country' = country -- omit if country = null
  )
$$ LANGUAGE sql STABLE;

At least one variable should be passed in so as to avoid returning all rows.

But the main reason I am asking before going too far down this rabbit hole is that I am sure I am thinking about this in completely the wrong way. There has to be a more standard way of handling this.

As an aside, another thing I can't work out is how to avoid hardcoding the SELECT statement SELECT id, data, version. SELECT * fails due to an extra value column that presumably comes from jsonb_array_elements(data -> 'addresses') addresses. I don't need this final value in the result.

1 Answer 1

1

The usual way to deal with that are conditions like this:

where (city is null or addresses ->> 'city' = city)
  and (state is null or addresses ->> 'state' = state)
  and (country is null or addresses ->> 'country' = country)

But OR conditions like that typically lead to slow queries.

Another option is to use the @> operator and gid rid of the unnesting completely:

SELECT gr.*
FROM research_db_gridreference gr
where gr.data -> 'addresses' @> jsonb_build_array(jsonb_strip_nulls(jsonb_build_object('city', city, 'state', state, 'country', country)));

The above assumes data is defined as jsonb (which it should be). If it's json you need to use the corresponding json_xxx functions.


how to avoid hardcoding the SELECT statement SELECT id, data, version

Actually that's the correct thing to do. select * is frowned upon in production code.

But you can easily get around the additional column if you only select columns from the table defined as the return type:

SELECT research_db_gridreference.*
FROM research_db_gridreference 
...
3
  • Thank you. That is very useful and works well.
    – dnk8n
    Jul 21, 2020 at 10:22
  • 1
    @DeanKayton: There is a button to express that. Vote up if you think the answer is very useful. Jul 21, 2020 at 12:06
  • 1
    I like the variant with @> jsonb_build_array(jsonb_strip_nulls(...)) a lot. With filters on plain columns (not nested JSON values), I would consider building the query dynamically to get optimal performance. See: stackoverflow.com/a/17355733/939860 Jul 21, 2020 at 12:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.