I’ve inherited the task of creating a queries in PostgreSQL but I’m not particularly familiar with it. I’ve been looking at some of the existing queries that were left behind and I came across this:
select data -> 'Fields' ->> 'name', p.* from table_1 as p
join table_2 as cr on cr.id = p.entity_id
where p.entity_id = 'series-of-numbers-and-characters'
I have a vague idea of what the rest of the query is doing, but what in the world does "data" mean in this context? It’s not a column. It’s not a table.
I see in DBeaver that it’s highlighted in blue, so I can tell it has some sort of special meaning. Right? Searching for "postgresql data" and similar rephrasing is absurdly unhelpful in how bland and generic the wording is. What exactly does "data" mean here? What is it doing to the query?
>Solution :
It’s been a while since I’ve worked with JSON data in PostgreSQL, but if I’m reading your query right, "data" is just a json or jsonb type column in one of the tables in the query (table_2 if I had to guess). The query is extracting as JSON the ‘Fields’ member of the json object represented by "data", then from that extracting as text the ‘name’ member.