Database Reference
In-Depth Information
data type, which is a binary form of JSON that can also take advantage of indexes. We'll
cover mostly JSON functions and operators introduced in version 9.3. We'll also show
you how to use
jsonb
, functions it shares with its
json
brethren, and new operators it
supports. Refer to
JSON Functions and Operators
for a full listing.
Inserting JSON Data
To create a table to hold
json
objects, define a column as a
json
type:
CREATE
TABLE
families_j
(
id
serial
PRIMARY
KEY
,
profile
json
);
Example 5-23
inserts JSON data. PostgreSQL will validate the input to make sure what
you are adding is valid JSON.
Example 5-23. Populating JSON field
INSERT
INTO
families_j
(
profile
)
VALUES
(
'{"name":"Gomez", "members":[
{"member":{"relation":"padre", "name":"Alex"}},
{"member":{"relation":"madre", "name":"Sonia"}},
{"member":{"relation":"hijo", "name":"Brandon"}},
{"member":{"relation":"hija", "name":"Azaleah"}}
]}'
);
You can't cast invalid JSON strings to the
json
type, nor can you
store invalid JSON strings in a
json
column. PostgreSQL conducts
background checks to ensure that the JSON string is well-behaved
before letting it take up residency in the database.
Querying JSON
New in version 9.3 are various functions for inspecting JSON data.
Example 5-24
uses
json_extract_path
,
json_array_elements
, and
json_extract_path_text
to obtain
family members.
Example 5-24. Query subelements
SELECT
json_extract_path_text
(
profile
,
'name'
)
As
family
,
json_ex
tract_path_text
(
json_array_elements
(
json_extract_path
(
profile
,
'mem
bers'
)
),
'member'
,
'name'
)
As
member
FROM
families_j
;
family |member
-------+------
Gomez |Alex
Gomez |Sonia
Gomez |Brandon
Gomez |Azaleah
Return name of family as text.
