[FIXED] How to Join PostgreSQL JSONB Data with Foreign Table

Issue

I have a PostgreSQL database table with a JSONB column that contains keys to a foreign table.

The structure looks something like this.

Events
_ _ _ _

id:uuid
name: uuid
fields: JSONB { 
      date {},
      activities: ['uuid','uuid']
           }
...others columns

Activities
_ _ _ _ _ _ 

id: UUID
name: varchar

I am trying to use the query below to join the tables but I get cannot cast type JSONB to uuid[] or set-returning functions are not allowed

SELECT
  to_json(src) :: json AS event
FROM
  (
    SELECT
      events.id AS id,
      events.name AS name,
      events.fields -> 'time' AS time,
      events.fields ->> 'poster' AS poster,
      events.fields ->> 'address' AS address,
      categories.name AS category,
      -- array_agg(DISTINCT activities.name) AS activities,
      events.age_limit AS age_limit,
      min(tickets.price) AS least_ticket,
      events.description AS description,
      to_json(profiles.*) AS organizer,
      timezones.short_code AS timezone,
      array_agg(DISTINCT tickets.*) AS tickets,
      to_json(currencies.*) AS currency
    FROM
      events
      LEFT JOIN categories ON categories.id = (events.fields ->> 'category_id') :: uuid
      LEFT JOIN activities ON activities.id = Any(
        jsonb_build_array(events.fields ->> 'activities') :: uuid [ ]
      ) 
      LEFT JOIN tickets ON tickets.event_id = events.id
      LEFT JOIN profiles ON events.user_id = profiles.user_id
      LEFT JOIN currencies ON currencies.id = events.currency_id
      LEFT JOIN timezones ON events.timezone_id = timezones.id
    WHERE
      events.slug = 'annes-wedding-nqbh'
    GROUP BY
      events.name,
      categories.id,
      events.id,
      profiles.id,
      currencies.id,
      timezones.id
  ) src;

Please what's the best way I can join these tables and also in the case where the activities field has an array with keys & values.

Solution

You cannot cast jsonb to uuid, but you should be able to cast the jsonb array element to text, and that to uuid (assuming that's how you store UUIDs in JSON). But notice you will need to do that individually, not for the whole array at once:

…
LEFT JOIN activities ON activities.id IN (
  SELECT el::uuid
  FROM jsonb_array_elements_text(events.fields -> 'activities') AS el
)

Answered By - Bergi

Answer Checked By - Pedro (PHPFixing Volunteer)

Read More

[FIXED] How to update a jsonb column with a replaced value in pgAdmin?

Issue

I have a PostgreSQL table called files which includes a jsonb table called formats. While some rows are [null], others have objects with this structure:

{
  "thumbnail": {
    "ext": ".jpg",
    "url": "https://some-url.com/image01.jpg",
    "name": "image01.jpg",
    //...other properties
  }
}

For every row I want to update the thumbnail.url and replace some-url with other-url.

I'm far from being an expert in PostgreSQL (or any other DB for that matter), and after some reading I tried to run the following query in pgAdmin:

UPDATE files 
  SET formats = jsonb_set(formats, '{thumbnail.url}', REPLACE('{thumbnail.url}', 'some-url', 'other-url'))

And I received this error: function jsonb_set(jsonb, unknown, text) does not exist

I tried to set format jsonb_set(formats::jsonb...), tried to target '{thumbnail}' instead of '{thumbnail.url}' - always the same error.

What am I doing wrong? Or is pgAdmin really doesn't support this function? How can I do such an update with pgAdmin query tool?

Solution

We can try to use ->> to get JSON content value of url and then replace your expect value from that.

Because your url field of your JSON might be string type we need to use " to content it before cast as JSONB

jsonb_set(target jsonb, path text[], new_value jsonb [, create_missing boolean])

UPDATE files 
SET formats = jsonb_set(formats, '{thumbnail,url}', CONCAT('"',REPLACE(formats->'thumbnail'->>'url','some-url','other-url'),'"')::JSONB);

sqlfiddle

Answered By - D-Shih

Answer Checked By - Marilyn (PHPFixing Volunteer)

Read More

[FIXED] How to perform update operations on columns of type JSONB in Postgres 9.4

Issue

Looking through the documentation for the Postgres 9.4 datatype JSONB, it is not immediately obvious to me how to do updates on JSONB columns.

Documentation for JSONB types and functions:

http://www.postgresql.org/docs/9.4/static/functions-json.html http://www.postgresql.org/docs/9.4/static/datatype-json.html

As an examples, I have this basic table structure:

CREATE TABLE test(id serial, data jsonb);

Inserting is easy, as in:

INSERT INTO test(data) values ('{"name": "my-name", "tags": ["tag1", "tag2"]}');

Now, how would I update the 'data' column? This is invalid syntax:

UPDATE test SET data->'name' = 'my-other-name' WHERE id = 1;

Is this documented somewhere obvious that I missed? Thanks.

Solution

Ideally, you don't use JSON documents for structured, regular data that you want to manipulate inside a relational database. Use a normalized relational design instead.

JSON is primarily intended to store whole documents that do not need to be manipulated inside the RDBMS. Related:

• JSONB with indexing vs. hstore

Updating a row in Postgres always writes a new version of the whole row. That's the basic principle of Postgres' MVCC model. From a performance perspective, it hardly matters whether you change a single piece of data inside a JSON object or all of it: a new version of the row has to be written.

Thus the advice in the manual:

JSON data is subject to the same concurrency-control considerations as any other data type when stored in a table. Although storing large documents is practicable, keep in mind that any update acquires a row-level lock on the whole row. Consider limiting JSON documents to a manageable size in order to decrease lock contention among updating transactions. Ideally, JSON documents should each represent an atomic datum that business rules dictate cannot reasonably be further subdivided into smaller datums that could be modified independently.

The gist of it: to modify anything inside a JSON object, you have to assign a modified object to the column. Postgres supplies limited means to build and manipulate json data in addition to its storage capabilities. The arsenal of tools has grown substantially with every new release since version 9.2. But the principal remains: You always have to assign a complete modified object to the column and Postgres always writes a new row version for any update.

Some techniques how to work with the tools of Postgres 9.3 or later:

• How do I modify fields inside the new PostgreSQL JSON datatype?

This answer has attracted about as many downvotes as all my other answers on SO together. People don't seem to like the idea: a normalized design is superior for regular data. This excellent blog post by Craig Ringer explains in more detail:

• "PostgreSQL anti-patterns: Unnecessary json/hstore dynamic columns"

Another blog post by Laurenz Albe, another official Postgres contributor like Craig and myself:

• JSON in PostgreSQL: how to use it right

Answered By - Erwin Brandstetter

Answer Checked By - Clifford M. (PHPFixing Volunteer)

Read More

[FIXED] how to update JSONB column using knexjs, bookshelfjs

Issue

I have a JSONB column in PostgreSQL database like {lat: value, lon: value}. I want to change any specific value at a time eg. lat, but I am not sure how I can achieve this using bookshelf.js or knex.js. I tried using jsonb_set() method specified in Postgres documentation but I am not sure if I used that correctly. Can somebody please suggest me how can I do this? or what is the correct syntax to do this? Thanks.

Solution

AFAIK only knex based thing that supports writing to and extracting data from postgresql jsonb columns is objection.js ORM.

With plain knex you need to use raw to write references:

knex('table').update({
  jsonbColumn: knex.raw(`jsonb_set(??, '{lat}', ?)`, ['jsonbColumn', newLatValue])
})

You can check generated SQL here https://runkit.com/embed/44ifdhzxejf1

Originally answered in: https://github.com/tgriesser/knex/issues/2264

More examples how to use jsonb_set with knex can be found in following answers

How to update a jsonb column's field in PostgreSQL?

What is the best way to use PostgreSQL JSON types with NodeJS

Answered By - Mikael Lepistö

Answer Checked By - Mary Flores (PHPFixing Volunteer)

Read More

[FIXED] How to update entire JSON object in JSONB postgres column except 1 field

Issue

for example I have a table:

CREATE TABLE fruit(id bigint, data jsonb);

and a row for example is:

1,    
{
   "type": "pinapple",
   "store1": {
   "first_added": "<some timestamp>",
   "price": "10",
   "store_id": "1",
   "comments": "some comments..."
},
   "store2": {
   "first_added": "<some timestamp>",
   "price": "11",
   "store_id": "2",
   "comments": "some comments..."
},
   .... more stores
}

In case of update I have the fruit id and store data :

1,
"store1": {
            "price": "12",
            "store_id": "1",
            "comments": "some comments...V2"
        }

I want to update entire store object in fruit entry (for store1), except the first_added field.

Any idea how I can accomplish it via JSONB operators or functions?

Thanks

Solution

You can use

UPDATE fruit
SET data = data || jsonb_set($1::jsonb, '{store1,first_added}', data#>'{store1,first_added}')
WHERE id = 1;

^{(online demo)}
where the parameter $1 is set to the value {"store1": {"price": "12", "store_id": "1", "comments": "some comments...V2"}}.

Or if you need the key to be dynamic, use

UPDATE fruit
SET data = jsonb_set(data, ARRAY[$2::text], jsonb_set($1::jsonb, '{first_added}', data->$2->'first_added'))
WHERE id = 1;

^{(online demo)}

Answered By - Bergi

Answer Checked By - Marilyn (PHPFixing Volunteer)

Read More