How to get JSON value in PostgreSQL database

There is a column in the PostgreSQL database called JSON. To get the data in JSON, you can use the following sql:

select orderno as OrderNo
 ,amount as Amount
 ,ordertime as OrderTime
 , recordtype as RecordType
from jsonb_to_recordset(( --Specific methods
  select array_to_json(array_agg(data)) --Convert to an array
  from wallet_details
  where id = @id
  )::jsonb) as x(orderno text, amount numeric(16, 6), ordertime text, recordtype varchar(32));

If you get the data as the current row, but you also need to get several values in JSON, you can get it in the following way:

select pay_params::json->>'Key' as Md5Key ,
  pay_params::json->>'AppId' as Appid ,
  pay_params::json->>'MchId' as Mchid ,
  pay_params::json->>'SubMchId' as Submchid ,
  tenant_id as Tenant_Id
  from spm_wallet_settings where id='12'

Supplement: PostgreSql database sql statement takes Json value

1: json field instance:

{
“boxNum”: 0,
“orderNum”: 0,
“commentNum”: 0
}

A. Take the value of boxNum

1.1) select field name ->'boxNum' from table name;

1.2) select jsonb_extract_path_text field name, ‘boxNum') from table name;

2: json field instance:

{
“boxNum”: “0”,
“orderNum”: “0”,
“commentNum”: “0”
}

A. Take the value of boxNum without double quotes.

2.1) select field name ->>'boxNum' from table name;

2.2) select jsonb_extract_path_text field name, ‘boxNum') from table name;

3: json field instance:

{
“unitPrices”: [{
“price”: 10.0,
“unitId”: “8”,
“unitName”: “500gram”,
“unitAmount”: “0”,
“isPMDefault”: true,
“isHomeDefault”: true,
“originalPrice”: 10.0
}],
“productName”: “Oceanwide Canada Lobster Wild fishing”,
“productType”: 1,
“skuPortRate”: {
“id”: “a6b83048-3878-4698-88c2-2a9de288ac56”,
“cityId”: “2bf8c60c-789d-433a-91ae-8e4ae3e587a4”,
“dynamicProperties”: [{
“name”: “mortality rate”,
“propertiesId”: “f05bda8c-f27c-4cc6-b97e-d4bd07272c81”,
“propertieValue”: {
“value”: “2.0”
}
}, {
“name”: “Water loss rate”,
“propertiesId”: “ee9d95d7-7e28-4d54-b572-48ae64146c46”,
“propertieValue”: {
“value”: “3.0”
}
}]
},
“quotePriceAttribute”: {
“currencyName”: “RMB”
}
}

A. Take the currencyName currency name in quotePriceAttribute

select (Field name&gt;&gt;‘quotePriceAttribute')::json-&gt;&gt;‘currencyName' from Table name；

B. Take the price unit price in unitPrices

select jsonb_array_elements((Field name-&gt;&gt;‘unitPrices')::jsonb)-&gt;&gt;‘price' from Table name；

C. Take the name of dynamicProperties in skuPortRate as the value in the propertyValue of the mortality rate;

select bb-&gt;‘propertieValue'-&gt;&gt;‘value' as value from (
select jsonb_array_elements(((Field name-&gt;&gt;‘skuPortRate')::json-&gt;&gt;‘dynamicProperties')::jsonb) as bb from Table name) as dd where  @&gt; ‘{“name”: “mortality rate”}';

Field instance:

[{“name”: “Fishing method”, “showType”: 4, “propertiesId”: “9a14e435-9688-4e9b-b254-0e8e7cee5a65”,
“propertieValue”: {“value”: “Wild fishing”, “enValue”: “Wild”}},
{“name”: “Processing method”, “showType”: 4, “propertiesId”: “7dc101df-d262-4a75-bdca-9ef3155b7507”,
“propertieValue”: {“value”: “Single freezing”, “enValue”: “Individual Quick Freezing”}},
{“name”: “country of origin”, “showType”: 4, “propertiesId”: “dc2b506e-6620-4e83-8ca1-a49fa5c5077a”,
“propertieValue”: {“value”: “Ireland”, “remark”: “”, “enValue”: “Ireland”}}]

– Obtain the origin

select
(SELECT ss-&gt;‘propertieValue' as mm FROM
(SELECT jsonb_array_elements (dynamic_properties) AS ss FROM product
where id=) as dd where  @&gt; ‘{“name”: “country of origin”}')-&gt;&gt;‘value' as cuntry,
a.*
from product as a where =‘633dd80f-7250-465f-8982-7a7f01aaeeec'；

5: json example:huren:["aaa", "bbb", "ccc"…]

Requirement: Take the value aaa and go to "double quotes"

select replace(cast(jsonb_array_elements(huren) as text), ‘"','') from XXX limit 1

The above is personal experience. I hope you can give you a reference and I hope you can support me more. If there are any mistakes or no complete considerations, I would like to give you advice.