return values of particular key if a string is present in an array

x89 0
Viewed 33

I run a simple select * query that returns an output like this:

[{
  "polarity": "0.0",
  "magnitude": "2.0",
  "score": "0.5",
  "entities": [{
    "name": "Taubenkot",
    "type": "OTHER",
    "mid": "",
    "wikipediaUrl": "",
    "numMentions": "1",
    "avgSalience": "0.150263"
  }, {
    "name": "Lösung",
    "type": "OTHER",
    "mid": "",
    "wikipediaUrl": "",
    "numMentions": "1",
    "avgSalience": "0.145794"
  }, {
    "name": "Busbahnhof",
    "type": "LOCATION",
    "mid": "",
    "wikipediaUrl": "",
    "numMentions": "1",
    "avgSalience": "0.108006"
  }, {
    "name": "Stadt",
    "type": "LOCATION",
    "mid": "",
    "wikipediaUrl": "",
    "numMentions": "3",
    "avgSalience": "0.079928"
  }, {
    "name": "Taubenplage",
    "type": "OTHER",
    "mid": "",
    "wikipediaUrl": "",
    "numMentions": "2",
    "avgSalience": "0.072326"
  }, {
    "name": "Rutschgefahr",
    "type": "OTHER",
    "mid": "",
    "wikipediaUrl": "",
    "numMentions": "1",
    "avgSalience": "0.070839"
  },
{
    "name": "Meike Böschemeyer",
    "type": "PERSON",
    "mid": "/g/11btlnnjgf",
    "wikipediaUrl": "",
    "numMentions": "1",
    "avgSalience": "0.00451"
  }]
}]

"Entities" is an array. I want to select all rows where the "type" = "LOCATION" is present in one of the entities values. In this case, I want to return the "name" value of that entity entry as a new column NAME.

something like "select .... AS NAME". How can I achieve this?

sql postgresql json arrays
2 Answers

First flatten the JSON field. See DB Fiddle

select l ->> 'name' as "Name", 
      (l ->> 'avgSalience')::numeric as "avgSalience"
      -- , other expressions
from the_table 
cross join lateral json_array_elements(j -> 0 -> 'entities') l
where l ->> 'type' = 'LOCATION';
Stefanov.sm0

Another option to flatten the JSON array is to use a JSON path expression:

The following returns the names as rows.

select l.item ->> 'name' as name
from the_table t
  cross join jsonb_path_query(t.the_column, '$.entities[*] ? (@.type == "LOCATION")') as l(item)

Another option is to return the names as a single JSON array:

select jsonb_path_query_array(t.the_column, '$.entities[*] ? (@.type == "LOCATION").name') as names
from the_table t

This assumes that the column is defined with the data type jsonb (which it should be). If it's not you need to cast it: the_column::jsonb

Online example

a_horse_with_no_name0
Related