2

How is it possible to open the JSONB column where JSONB string with slashes?

"{\"id\":\"c39fe0f5f9b7c89b005bf3491f2a2ce1\",\"token\":\"c39fe0f5f9b7c89b005bf3491f2a2ce1\",\"line_items\":[{\"id\":32968150843480,\"properties\":{},\"quantity\":2,\"variant_id\":32968150843480,\"key\":\"32968150843480:4a6f6b7d19c7aef119af2cd909f429f1\",\"discounted_price\":\"40.00\",\"discounts\":[],\"gift_card\":false,\"grams\":0,\"line_price\":\"80.00\",\"original_line_price\":\"80.00\",\"original_price\":\"40.00\",\"price\":\"40.00\",\"product_id\":4638774493272,\"sku\":\"36457537-mud-yellow-28\",\"taxable\":false,\"title\":\"Knee Length Summer Shorts - Camel / 28\",\"total_discount\":\"0.00\",\"vendor\":\"Other\",\"discounted_price_set\":{\"shop_money\":{\"amount\":\"40.0\",\"currency_code\":\"USD\"},\"presentment_money\":{\"amount\":\"40.0\",\"currency_code\":\"USD\"}},\"line_price_set\":{\"shop_money\":{\"amount\":\"80.0\",\"currency_code\":\"USD\"},\"presentment_money\":{\"amount\":\"80.0\",\"currency_code\":\"USD\"}},\"original_line_price_set\":{\"shop_money\":{\"amount\":\"80.0\",\"currency_code\":\"USD\"},\"presentment_money\":{\"amount\":\"80.0\",\"currency_code\":\"USD\"}},\"price_set\":{\"shop_money\":{\"amount\":\"40.0\",\"currency_code\":\"USD\"},\"presentment_money\":{\"amount\":\"40.0\",\"currency_code\":\"USD\"}},\"total_discount_set\":{\"shop_money\":{\"amount\":\"0.0\",\"currency_code\":\"USD\"},\"presentment_money\":{\"amount\":\"0.0\",\"currency_code\":\"USD\"}}}],\"note\":null,\"updated_at\":\"2022-03-15T13:24:02.787Z\",\"created_at\":\"2022-03-15T13:23:31.912Z\",\"controller\":\"custom_webhooks\",\"action\":\"store_data\",\"custom_webhook\":{\"id\":\"c39fe0f5f9b7c89b005bf3491f2a2ce1\",\"token\":\"c39fe0f5f9b7c89b005bf3491f2a2ce1\",\"line_items\":[{\"id\":32968150843480,\"properties\":{},\"quantity\":2,\"variant_id\":32968150843480,\"key\":\"32968150843480:4a6f6b7d19c7aef119af2cd909f429f1\",\"discounted_price\":\"40.00\",\"discounts\":[],\"gift_card\":false,\"grams\":0,\"line_price\":\"80.00\",\"original_line_price\":\"80.00\",\"original_price\":\"40.00\",\"price\":\"40.00\",\"product_id\":4638774493272,\"sku\":\"36457537-mud-yellow-28\",\"taxable\":false,\"title\":\"Knee Length Summer Shorts - Camel / 28\",\"total_discount\":\"0.00\",\"vendor\":\"Other\",\"discounted_price_set\":{\"shop_money\":{\"amount\":\"40.0\",\"currency_code\":\"USD\"},\"presentment_money\":{\"amount\":\"40.0\",\"currency_code\":\"USD\"}},\"line_price_set\":{\"shop_money\":{\"amount\":\"80.0\",\"currency_code\":\"USD\"},\"presentment_money\":{\"amount\":\"80.0\",\"currency_code\":\"USD\"}},\"original_line_price_set\":{\"shop_money\":{\"amount\":\"80.0\",\"currency_code\":\"USD\"},\"presentment_money\":{\"amount\":\"80.0\",\"currency_code\":\"USD\"}},\"price_set\":{\"shop_money\":{\"amount\":\"40.0\",\"currency_code\":\"USD\"},\"presentment_money\":{\"amount\":\"40.0\",\"currency_code\":\"USD\"}},\"total_discount_set\":{\"shop_money\":{\"amount\":\"0.0\",\"currency_code\":\"USD\"},\"presentment_money\":{\"amount\":\"0.0\",\"currency_code\":\"USD\"}}}],\"note\":null,\"updated_at\":\"2022-03-15T13:24:02.787Z\",\"created_at\":\"2022-03-15T13:23:31.912Z\"}}"

this real JSONB column enter image description here

I can not find any example of how to deal with this type of JSONB

Improve this question
8
  • Looks like a problem with your tooling. What tool do you use? Commented Mar 15, 2022 at 15:12
  • I use PG Admin 4 Commented Mar 15, 2022 at 15:19
  • What options do you have to not display all these quotes " ? Because it's just a problem with displaying the results, in your database everything is fine. Commented Mar 15, 2022 at 15:43
  • I just need to extract data from jsonb to columns and rows like a simple table Commented Mar 15, 2022 at 15:57
  • All these slashes and extra quotes, are a result of PG Admin4. Change it's display setting, if possible, or use a different tool. And turning a JSON object in a row/column layout, can be done using the json-functions in PostgreSQL: postgresql.org/docs/current/functions-json.html Commented Mar 15, 2022 at 17:09

2 Answers 2

Reset to default
4

Whatever is inserting your data is screwing it up. It is taking the string representation of a JSON object and stuffing that into a JSON string scalar. You need to fix that or it will just keep happening.

To fix what is already there, you need to extract the real PostgreSQL string out of the JSON string, then cast that to JSONB. Extracting a JSON string can be done unintuitively with #>>'{}', or even less intuitively with ->>0.

select (data#>>'{}')::jsonb from table_name.

Of course you should fix it permanently, not just do it on the fly all the time, which is both slow and confusing.

update table_name set data=(data#>>'{}')::jsonb;

Of course fixing the tool which screws this up in the first place, and fixing the historical data, need to be done in a coordinated fashion or you will have a glorious mess on your hands.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Using a BEFORE trigger to clean up the incoming mess could help to get the problem under control.
0

I think you have wrong formatted string in jsonb field. You can try fix it in next way:

select trim(both '"' from replace(data::varchar, '\"', '"'))::jsonb data from tbl;

PostgreSQL JSONB online

Improve this answer

1 Comment

Hi, thanks for your answer. This method does not work in any situation. For example, I get error like :ERROR: invalid input syntax for type json , CONTEXT: JSON data, line 1: ...ghgate Junior School Tracksuit - 24\\"","...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.