sql如何获取字段里的json值

Postgres

-- 语法
cast((字段名->>'数列名') as numeric)/10^18 as 标识名,

直接上示例代码吧

Json源数据

{"Info": 
    {"/": "bafy2bzacebmi36ddo24dq53dgsgocfptediwrdg4b5wqz5ywsinga4oueoi56"},   
    "FeeDebt": "0",
    "Sectors": 
        {"/": "bafy2bzacedswlcz5ddgqnyo3sak3jmhmkxashisnlpq6ujgyhe4mlobzpnhs6"}, 
    "Deadlines": 
        {"/": "bafy2bzaceakubxbluycf7ug7ve3o5yg2kwbhjaf65gtu5m4ethfrq2qxurtcy"}, 
    "LockedFunds": "0", 
    "VestingFunds": 
        {"/": "bafy2bzacealbq6s7ptdud6gvpc2yv54opwotncjlqjxmzb2q2rnjxv753rwdc"}, 
    "InitialPledge": "0", 
    "CurrentDeadline": 9, 
    "AllocatedSectors": 
        {"/": "bafy2bzacea456askyutsf7uk4ta2q5aojrlcji4mhaqokbfalgvoq4ueeh4l2"}, 
    "EarlyTerminations": [0], 
    "PreCommitDeposits": "0", 
    "ProvingPeriodStart": 154656, 
    "PreCommittedSectors": 
        {"/": "bafy2bzaceamp42wmmgr2g2ymg46euououzfyck7szknvfacqscohrvaikwfay"}, 
    "PreCommittedSectorsExpiry": 
        {"/": "bafy2bzacedswlcz5ddgqnyo3sak3jmhmkxashisnlpq6ujgyhe4mlobzpnhs6"}
}

SELECT
	  epoch,
    balance/10^18 as balance,
	  available_balance/10^18 AS available_balance,
	  cast((state->>'InitialPledge') as numeric)/10^18 as InitialPledge,
	  cast((state->>'LockedFunds') as numeric)/10^18 as LockedFunds,
	  cast((state->>'PreCommitDeposits') as numeric)/10^18 as PreCommitDeposits,
	  cast((state->>'FeeDebt') as numeric)/10^18 as FeeDebt
FROM
	lotus_miners
WHERE
	actor = 'f02438'
ORDER BY
	epoch DESC
	LIMIT 1;